Tom,

Can you either explain the gory details about how flashback data archive works or point me to where I can read about it? (Not how to set it up, how the storage works, where is it stored, how is it stored, does it get backed up, can I access it etc....) Thanks.

How and when does time based query decides that it is time to use flashback archive vs. reconstructing time-consistent buffer from dirty block and undo? From the documentation of total recall, it is clear that the data in the flashback archive is available by FBDA process before UNDO is reused for another transaction.

It appears that reading time consistent data from large undo tablespace with high undo retention on frequently modified blocks seems more time consuming (in 10g) than reading from from flashback data archive(11gr2).

How does
Oracle time based query in 11gR2 chooses to start using the flashback archive vs. using dirty blocks + undo segments?

Hi Tom,
   I notice some strange behaviour concerning flashback archive, wonder if you can shed some light on this:

SQL> select archive_table_name from dba_flashback_archive_tables where table_name='T_FB_NEW';

ARCHIVE_TABLE_NAME
-----------------------------------------------------
SYS_FBA_HIST_71973
SQL> select *  from sys_fba_hist_71973;

RID                    STARTSCN     ENDSCN XID              O          A
-------------------- ---------- ---------- ---------------- - ----------
AAARklAAEAAAA8mAAC      2951980    3017370 020001007A050000 I          3
AAARklAAEAAAA8lAAA      3016152    3057249 07000D00DB040000 I          5
AAARklAAEAAAA8lAAB      3016152    3057249 07000D00DB040000 I          6
AAARklAAEAAAA8lAAC      3016152    3057249 07000D00DB040000 I          7
AAARklAAEAAAA8lAAD      3016152    3057249 07000D00DB040000 I          8
AAARklAAEAAAA8mAAA      2951980    3057249 020001007A050000 I          1
AAARklAAEAAAA8mAAB      2951980    3057249 020001007A050000 I          2
AAARklAAEAAAA8mAAC      3017370    3057249 06001B00E3050000 U         66

8 rows selected.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> create undo tablespace UNDOTBS2 datafile '?\oradata\car11\undotbs2.dbf' size 100m autoallocate;
create undo tablespace UNDOTBS2 datafile '?\oradata\car11\undotbs2.dbf' size 100m autoallocate
*
ERROR at line 1:
ORA-01119: error in creating database file '?\oradata\car11\undotbs2.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.


SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE11\oradata\car11\undotbs2.dbf' size 100m autoallocate;

Tablespace created.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS2' scope=both;

System altered.

SQL> select *  from sys_fba_hist_71973;

RID                    STARTSCN     ENDSCN XID              O          A
-------------------- ---------- ---------- ---------------- - ----------
AAARklAAEAAAA8mAAC      2951980    3017370 020001007A050000 I          3
AAARklAAEAAAA8lAAA      3016152    3057249 07000D00DB040000 I          5
AAARklAAEAAAA8lAAB      3016152    3057249 07000D00DB040000 I          6
AAARklAAEAAAA8lAAC      3016152    3057249 07000D00DB040000 I          7
AAARklAAEAAAA8lAAD      3016152    3057249 07000D00DB040000 I          8
AAARklAAEAAAA8mAAA      2951980    3057249 020001007A050000 I          1
AAARklAAEAAAA8mAAB      2951980    3057249 020001007A050000 I          2
AAARklAAEAAAA8mAAC      3017370    3057249 06001B00E3050000 U         66

8 rows selected.
SQL> select *  from t_fb_new versons between scn minvalue and maxvalue;
select *  from t_fb_new versons between scn minvalue and maxvalue
                                *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> select *  from t_fb_new versions between scn minvalue and maxvalue;
select *  from t_fb_new versions between scn minvalue and maxvalue
               *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9_1192467665$" too small

SQL> select *  from t_fb_new versions between scn 2951980 and 3017370;
select *  from t_fb_new versions between scn 2951980 and 3017370
               *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9_1192467665$" too small

SQL> SELECT *  FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production


My question is, 

1) I found that not all the changes to the table is capture into sys_fba_hist_71973(Not timely updated flashback archive table);
2) After I switch the undo tablespace, oracle should query the data from flashback archive instead of snapshot too old?

   Would you pls kindly help above?

Thank you!

Hi Tom,
  this is a full example:

C:\Documents and Settings\Administrator>sqlplus carson/password

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Oct 6 10:38:18 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS3
SQL> create table f1 ( a number);

Table created.

SQL> alter table f1 flashback archive;

Table altered.

SQL> insert into f1 values(1);

1 row created.

SQL> insert into f1 values(2);

1 row created.

SQL> insert into f1 values(3);

1 row created.

SQL> insert into f1 values(4);

1 row created.

SQL> insert into f1 values(5);

1 row created.

SQL> commit;

Commit complete.

SQL> col flashback_archive_name format a10
SQL> select *  from dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME                     FLASHBACK_ ARCHIVE_TABLE_NAME
------------------------------ ------------------------------ ---------- ----------------------------------------------
T_FB                           CARSON                         FBDA_1     SYS_FBA_HIST_71946
T_FB_NEW                       CARSON                         FBDA_1     SYS_FBA_HIST_71973
T_FB_2                         CARSON                         FBDA_1     SYS_FBA_HIST_72090
F1                             CARSON                         FBDA_1     SYS_FBA_HIST_72102

SQL> select *  from carson.SYS_FBA_HIST_72102;

no rows selected

SQL> show user
USER is "CARSON"
SQL> insert into f1(a) select object_id from user_objects;

66 rows created.

SQL> commit;

Commit complete.

SQL> select *  from carson.SYS_FBA_HIST_72102;

no rows selected

SQL> select *  from carson.SYS_FBA_HIST_72102;

no rows selected

SQL> exec dbms_lock.sleep(180);

PL/SQL procedure successfully completed.

SQL> select *  from carson.SYS_FBA_HIST_72102;

no rows selected

SQL> select sysdate from date;
select sysdate from date
                    *
ERROR at line 1:
ORA-00903: invalid table name


SQL> select sysdate from dual;

SYSDATE
-------------------
2010-10-06 10:54:43

SQL> insert into f1(a) select object_id+10 from user_objects;

66 rows created.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2010-10-06 11:00:19

SQL> insert into f1(a) select object_id+100 from user_objects;

66 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_lock.sleep(180);

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE
-------------------
2010-10-06 11:04:34

SQL> select *  from sys_fba_hist_72102;

no rows selected

SQL> create undo tablespace undotbs4 datafile 'D:\Oracle11\oradata\car11\undotbs4.dbf' size 100m;

Tablespace created.

SQL> select *  from sys_fba_hist_72102;

no rows selected

SQL> select count(*) from f1 versions between timestamp to_timestamp('2010-10-06 11:00:19','yyyy-mm-dd hh24:mi:ss') and to_timestamp('2010-10-06 11:04:34','yyyy-mm-dd hh24:mi:ss');

  COUNT(*)
----------
       203

SQL> select *  from sys_fba_hist_72102;

no rows selected

SQL> alter system set undo_tablespace='UNDOTBS4' Scope=both;

System altered.

SQL> select count(*) from f1 versions between timestamp to_timestamp('2010-10-06 11:00:19','yyyy-mm-dd hh24:mi:ss') and to_timestamp('2010-10-06 11:04:34','yyyy-mm-dd hh24:mi:ss');

  COUNT(*)
----------
       203

SQL> select *  from sys_fba_hist_72102;

no rows selected

SQL>
SQL> select *  from f1 as of timestamp to_timestamp('2010-10-06 10:49:19','yyyy-mm-dd hh24:mi:ss');

         A
----------
         1
         2
         3
         4
         5

SQL>

my questions are:
1) I did not see sys_fba_hist_72101 populated from start to finish by flashback archive.
2) The strange thing is that even if I switched the undo, we still get as of timestamp result under the circumstance that sys_fba_hist_72101 is not populated.
  Can you shed some light?

Thank you.

Do not investigate too much on this kind of tables because:
1/ They are internal and so you cannot rely on what they content
2/ fbda implementation has changed in 11.2, these tables no more exist (at least they are no more exposed) and so the time you spent there is just wasted time

Regards
Michel

rajesh@ORA11GR2> select table_name, flashback_archive_name, status
  2  from dba_flashback_archive_tables;

TABLE_NAME FLASHBACK_ STATUS
---------- ---------- ----------
T1         FB1        ENABLED

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_flashback_archive.disassociate_fba(USER,'T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.00
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> truncate table t1;

Table truncated.

Elapsed: 00:00:00.10
rajesh@ORA11GR2>
rajesh@ORA11GR2> drop table t1 ;
drop table t1
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> drop flashback archive fb1;

Flashback archive dropped.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> drop table t1 purge;
drop table t1 purge
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2>

Tom:

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_flashback.htm#BJFJHDAG

<quote>
If you must use unsupported DDL statements on a table enabled for Flashback Data Archive, use the DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA procedure to disassociate the base table from its Flashback Data Archive.
</quote>

Even after disassociating FBA, Why I am able to Truncate table 'T1' but not able to drop.

Tom:

We are planning to use flashback data archive in 11g to audit transactional changes in our application. Also we are planning to partition the table and getting the error message while splitting partition.

So, Is that mean Flashback data archive is not supported on partitioned tables? Is there is any workaround for this?
http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#autoId30

rajesh@ORA11GR2> create table t(x number)
  2  partition by list(x)
  3  ( partition p_max values(default) )
  4  flashback archive fb1;

Table created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table t split partition
  2  p_max values(1) into
  3  (partition p_1,partition p_max);
alter table t split partition
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


Elapsed: 00:00:00.10
rajesh@ORA11GR2>

ops$tkyte%ORA11GR2> create flashback archive fb1 tablespace users retention 1 year;

Flashback archive created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t(x number)
  2    partition by list(x)
  3    ( partition p_max values(default) )
  4    flashback archive fb1;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_flashback_archive.DISASSOCIATE_FBA( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t split partition
  2    p_max values(1) into
  3    (partition p_1,partition p_max);

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_flashback_archive.REASSOCIATE_FBA( user, 'T' );

PL/SQL procedure successfully completed.

Tom:

exec dbms_flashback_archive.DISASSOCIATE_FBA( user, 'T' );

What happens

1) if one session disassociate FBA & split (default) partition.
2) The another session load data into another partition on the same table (say partition p_2)

The moment we diassociate FBA from another session, we will be missing out the changes in the underlying FDA respectively. Is this a good solution in a multiuser environment?

Tom:

We have a Web application which uses connection pooling to maintain connection in the database. When the connection gets reused in App server it will call dbms_session.set_identifier to set the client_identifier before making any calls / Transaction in database.

By using the below query i am able to track the Transaction start and end time also the type of the operation. But how do i know which user has performed these changes from App layer( using connection polling)?

rajesh@ORA11GR2>
rajesh@ORA11GR2> select t.*,
  2     versions_starttime,
  3     versions_endtime,
  4     versions_operation
  5  from t
  6  versions between timestamp minvalue and maxvalue;

         X Y         VERSIONS_STARTTIME             VERSIONS_ENDTIME               V
---------- --------- ------------------------------ ------------------------------ -
         1 16-DEC-11 16-DEC-11 12.39.46 AM                                         D
         1 16-DEC-11 16-DEC-11 12.39.46 AM          16-DEC-11 12.39.46 AM          I
         2 16-DEC-11 16-DEC-11 12.39.46 AM                                         I
         3 16-DEC-11 16-DEC-11 12.39.46 AM                                         I

Elapsed: 00:00:00.12
rajesh@ORA11GR2>

Tom:

1) Does this FBAR timer means, the time took by Flashback Data Archiver Process (Background process) to move the data from Undo Tablespace to Flashback data archive tablespace?

2)I am unable to find information about FBAR timer in product docs. Can you please point to correct link?

http://docs.oracle.com/cd/E11882_01/server.112/e25513/toc.htm

create table dept(deptno number,dname varchar2(40),loc varchar2(20))
nologging
flashback archive FB1;

insert into dept
select level as deptno,
dbms_random.string('A',30) as dname,
dbms_random.string('B',10) as loc
from dual
connect by level <=100000;

alter table dept
add constraint dept_pk
primary key(deptno);

Tkprof show's me this

alter table dept
add constraint dept_pk
primary key(deptno)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       2.10          0          6          3           0
Execute      1      0.01       8.15          0        766          4           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01      10.25          0        772          7           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  fbar timer                                     10        0.99          9.88
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Tom:

http://www.oracle.com/technetwork/issue-archive/2008/08-jul/o48totalrecall-092147.html
I was working on these examples. Where i have enabled Flashback data achive but i dont see those Archive tables in the database,

1) Is that i had to wait some more time to see them up?
2) Also i don't see them up in explain plan too.

rajesh@ORA11GR2> select table_name, archive_table_name, status
  2  from dba_flashback_archive_tables
  3  /

TABLE_NAME           ARCHIVE_TABLE_NAME   STATUS
-------------------- -------------------- --------
TEST_NAME            SYS_FBA_HIST_75619   ENABLED
TEST_RESULT          SYS_FBA_HIST_75621   ENABLED

Elapsed: 00:00:00.43
rajesh@ORA11GR2>
rajesh@ORA11GR2> select * from dba_objects
  2  where object_name like '%SYS_FBA_HIST_75619%'
  3  /

no rows selected

Elapsed: 00:00:00.25
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select * from
  2  med_test.test_result versions between timestamp minvalue and maxvalue
  3  where patient_id = 101 AND test_code = 'coca'
  4  AND test_time = TIMESTAMP '2008-02-01 13:17:00';
Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3901886979

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_RESULT    |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | TEST_RESULT_PK |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Tom:

Previously i dont see any entry in dba_objects for the below query. (you can see the same in above post)

rajesh@ORA11GR2> select * from dba_objects
  2  where object_name like '%SYS_FBA_HIST_75619%'
  3  /

no rows selected

Elapsed: 00:00:00.25
rajesh@ORA11GR2>

But, when i query now i am able to see them.

1) Can you help us to understand why there is a delay in creating these objects 'SYS_FBA_HIST_75619'? Why they are not created initially and available later?

rajesh@ORA11GR2> select object_name,object_type from dba_objects
  2  where object_name like '%SYS_FBA_HIST_75619%'
  3  /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SYS_FBA_HIST_75619             TABLE PARTITION
SYS_FBA_HIST_75619             TABLE

Elapsed: 00:00:00.26
rajesh@ORA11GR2>
rajesh@ORA11GR2>

Hi Tom,
I have no dedicated tablespace for the FDA. I am using an existing tablespace for this. I have enabled FDA for a table. I can see the history of the table using "as of" clause. I can also acknowledge this table for which FDA is enabled from the dba_flashback_archive_tables view.

But strange thing is that I can also query another table (for which FDA is not enabled) in the same tablespace, using "as of" clause and get its past point in time. For information, my database's flashback is OFF. How is this possible to use "as of" clause for a table for which the FDA is not active and the database is not in flashback mode. Is it really strange or normal?

How to check if the query uses FDA or the UNDO segments, when we use "as of" clause to get the table to former pint in time?

ops$tkyte%ORA11GR2> select * from fb_demo1 as of timestamp (SYSTIMESTAMP - INTERVAL '1' MINUTE);

Execution Plan
----------------------------------------------------------
Plan hash value: 982565668

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                     |    21 |   546 |    21  (10)| 00:00:01 |       |       |
|   1 |  VIEW                     |                     |    21 |   546 |    21  (10)| 00:00:01 |       |       |
|   2 |   UNION-ALL               |                     |       |       |            |          |       |       |
|*  3 |    FILTER                 |                     |       |       |            |          |       |       |
|   4 |     PARTITION RANGE SINGLE|                     |    17 |   918 |    14   (0)| 00:00:01 |   KEY |     1 |
|*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_122782 |    17 |   918 |    14   (0)| 00:00:01 |   KEY |     1 |
|*  6 |    FILTER                 |                     |       |       |            |          |       |       |
|   7 |     MERGE JOIN OUTER      |                     |     4 |  8264 |     7  (29)| 00:00:01 |       |       |
|   8 |      SORT JOIN            |                     |     4 |   152 |     3  (34)| 00:00:01 |       |       |
|*  9 |       TABLE ACCESS FULL   | FB_DEMO1            |     4 |   152 |     2   (0)| 00:00:01 |       |       |
|* 10 |      SORT JOIN            |                     |     1 |  2028 |     4  (25)| 00:00:01 |       |       |
|* 11 |       TABLE ACCESS FULL   | SYS_FBA_TCRV_122782 |     1 |  2028 |     3   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("TIMESTAMP_TO_SCN"(SYSTIMESTAMP(6)-INTERVAL'+00 00:01:00' DAY(2) TO SECOND(0))<86617383)
   5 - filter("ENDSCN"<=86617383 AND ("OPERATION" IS NULL OR "OPERATION"<>'D') AND
              "ENDSCN">"TIMESTAMP_TO_SCN"(SYSTIMESTAMP(6)-INTERVAL'+00 00:01:00' DAY(2) TO SECOND(0)) AND ("STARTSCN"
              IS NULL OR "STARTSCN"<="TIMESTAMP_TO_SCN"(SYSTIMESTAMP(6)-INTERVAL'+00 00:01:00' DAY(2) TO SECOND(0))))
   6 - filter("STARTSCN"<="TIMESTAMP_TO_SCN"(SYSTIMESTAMP(6)-INTERVAL'+00 00:01:00' DAY(2) TO SECOND(0))
              OR "STARTSCN" IS NULL)
   9 - filter("T"."VERSIONS_STARTSCN" IS NULL)
  10 - access("RID"(+)=ROWIDTOCHAR("T".ROWID))
       filter("RID"(+)=ROWIDTOCHAR("T".ROWID))
  11 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>86617383) AND ("STARTSCN"(+) IS NULL OR
              "STARTSCN"(+)<86617383))

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> select * from fb_demo2 as of timestamp (SYSTIMESTAMP - INTERVAL '1' MINUTE);

Execution Plan
----------------------------------------------------------
Plan hash value: 3382593906

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    82 |  2132 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| FB_DEMO2 |    82 |  2132 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

ops$tkyte%ORA11GR2> set autotrace off

We are starting a new project using Total Recall as the auditing solution and I was testing with a sample retention period in my local database. I finally got the actual retention periods needed, so I went back to my original table to change the retention period and I am running into some issues. Two questions:

1) The "solution" I used was to issue the "ALTER TABLE ___ NO FLASHBACK ARCHIVE" command. I am now getting an ORA-55624 when I try to give it a new archive. I have researched the error, and the solutions I have found is "try again later". It has now been 5 days and I have bounced the box about 10 times (the DB is on my laptop, so I shut it down when I am done working), but I am still getting this error. How long can this take to clear up and/or is there something I can do to clear this issue?

2) We don't plan on changing retention periods in production, but in the scenario that it turns out that we have to, what is the best way to do it? Is there a way to change the retention period without losing the data? Is the command I issued the right way to do it? I have tried searching for this information, but the keywords I have thought to use in the search don't seem to narrow the search down to what I am looking for.

Thank you,
Tim

I used TOAD to execute the script. I'll try the same command on another table using SQL*Plus and see what happens.

As for altering the retention period, we have about 30 tables and we have 3 retention periods, so we created 3 archives. Altering the retention period would affect the other tables assigned to that archive. Is it better practice to create an separate archive for each table you want to track? It certainly does seem to make changing the retention period easier.

Hi Tom,

Following up on the question about the 'AS OF' query syntax, how would you wrap a fully function total recall table?

In my case I tested my base Total recall table using a historical 'AS OF' timestamp and a restrictive where clause - this returned the correct row. When I wrapped the table inside a view and used the same syntax I got an ORA-01466 error.

select * from CUT_BLOCK_COMBO AS OF TIMESTAMP FROM_TZ(TO_TIMESTAMP('2007-08-28 00:00:00','YYYY-MM-DD HH24:MI:SS'), 'UTC') WHERE CB_SKEY = 4141;

-- returns one row

create or replace view CUT_BLOCK_COMBO_VW as select * from CUT_BLOCK_COMBO;

-- creates successfully

select * from CUT_BLOCK_COMBO_VW AS OF TIMESTAMP FROM_TZ(TO_TIMESTAMP('2007-08-28 00:00:00','YYYY-MM-DD HH24:MI:SS'), 'UTC') WHERE CB_SKEY = 4141;

-- ORA-01466: unable to read data - table definition has changed

ops$tkyte%ORA11GR2> create flashback archive fbda_test tablespace users quota 100 G retention 1 day;
Flashback archive created.


ops$tkyte%ORA11GR2> create table t ( x int, y int, z int ) flashback archive fbda_test;
Table created.

ops$tkyte%ORA11GR2> insert into t values ( 1, 2, 3 );
1 row created.

ops$tkyte%ORA11GR2> commit;
Commit complete.

ops$tkyte%ORA11GR2> column dt new_val D
ops$tkyte%ORA11GR2> select to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ) dt from dual;

DT
--------------------
31-may-2013 10:43:04

ops$tkyte%ORA11GR2> exec dbms_lock.sleep( 5 );
PL/SQL procedure successfully completed.


ops$tkyte%ORA11GR2> update t set x = x+1, y = y+1, z = z+1;
1 row updated.

ops$tkyte%ORA11GR2> create or replace view v_t as select * from t;
View created.

<b>Note when the view was created, after time D....</b>


ops$tkyte%ORA11GR2> create or replace view v_t2
  2  as
  3  select *
  4    from t
  5    as of timestamp to_timestamp( sys_context( 'userenv', 'client_info' ), 'dd-mon-yyyy hh24:mi:ss' );
View created.

<b>same with this view, but this view does the flashing back against T, we won't flashback the view...</b>

ops$tkyte%ORA11GR2> column dt new_val D2
ops$tkyte%ORA11GR2> select to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ) dt from dual;

DT
--------------------
31-may-2013 10:43:09

ops$tkyte%ORA11GR2> connect /
Connected.

ops$tkyte%ORA11GR2> exec dbms_lock.sleep( 300 );
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from v_t as of timestamp to_timestamp( '&D', 'dd-mon-yyyy hh24:mi:ss' );
old   1: select * from v_t as of timestamp to_timestamp( '&D', 'dd-mon-yyyy hh24:mi:ss' )
new   1: select * from v_t as of timestamp to_timestamp( '31-may-2013 10:43:04', 'dd-mon-yyyy hh24:mi:ss' )
select * from v_t as of timestamp to_timestamp( '31-may-2013 10:43:04', 'dd-mon-yyyy hh24:mi:ss' )
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

<b>we cannot go back to D since the view was created *after* D</b>

ops$tkyte%ORA11GR2> select * from v_t as of timestamp to_timestamp( '&D2', 'dd-mon-yyyy hh24:mi:ss' );
old   1: select * from v_t as of timestamp to_timestamp( '&D2', 'dd-mon-yyyy hh24:mi:ss' )
new   1: select * from v_t as of timestamp to_timestamp( '31-may-2013 10:43:09', 'dd-mon-yyyy hh24:mi:ss' )

         X          Y          Z
---------- ---------- ----------
         1          2          3

<b>but we can to back to D2, since D2 happened after the view was created...</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_application_info.set_client_info( '31-may-2013 10:43:10' );
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from v_t2;

         X          Y          Z
---------- ---------- ----------
         1          2          3

ops$tkyte%ORA11GR2> exec dbms_application_info.set_client_info( '31-may-2013 10:43:20' );
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from v_t2;

         X          Y          Z
---------- ---------- ----------
         2          3          4

<b>but the view on T AS OF TIMESTAMP works ok - because the flashing back is taking place against the table...</b>

Note that you seem to be querying the view AS OF a time prior to when the view was created. This is not necessarily the same thing as issuing the query that is currently defined in the view, as of that point in time.

I don't think there is any way you can query the view, as of a point in time prior to when the view existed.

Tom,

I have a EMP table having some 20M records and enabled FBDA.And provided access to audit information using a view (for end users and applications).

create or replace view emp_audit as 
select * 
from emp versions 
between timestamp 
minvalue and maxvalue ;

Let's say audit has some 50M records, and if i query (produces 10 rows in output) select * from emp_audit where empno = 6589; will an Index scan be possible here?

a) how does an index scan be possible on Oracle internally created FBDA table (SYS_FBA_HIST_.. and SYS_FBA_TCRV_..)

b) how can i index a specific column in audit table? say select * from emp_audit where comm <=10 (has only five rows) how can I index a specific column in audit tables? but I don't have index on comm column in emp table

but why are you using that sort of view???? it would be much easier just to say "as of timestamp :x" rather than have to always where on a date field?

1) We have an History screen in the application, when the end-user drill down from search screen to history screen, then we have to show all changes happened for this employee. (so we need Flashback version query NOT flashback query)

but why are you using that sort of view????

2) I don't want to train end users to write Flashback version query. I just expose them in a view (emp_audit) and end users say SELECT * FROM EMP_AUDIT where empno=? which leads to predicate pushing.

you can index things in the archive that are not indexed in the table -

3) This sound interesting can you show me how to do this? you mean create index on Oracle internally created FBDA table (SYS_FBA_HIST_.. and SYS_FBA_TCRV_..) ? I looked in docs at the below link, but I don't find any. can you help me?

http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#i1020531