flashback archive table

One of the problem with flashback queries in 10g and before is that you never know if it will works, especially you cannot expect to have flashback queries working for very old tables.

Let’s imagine you want to export your CUSTOMER as of 30/6/2007. No chance in 10g…

Well, with 11g, you can create a flashback archive, and it will save all change until end of retention (many years if you want).

Here it is :
SQL> connect / as sysdba
Connected.
SQL> create tablespace s;

Tablespace created.

SQL> create flashback archive default fba01 tablespace s
retention 1 month;

Flashback archive created.

SQL> connect scott/tiger
Connected.
SQL> create table t(x number) flashback archive;

Table created.

SQL> host sleep 10

SQL> insert into t(x) values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT dbms_flashback.get_system_change_number FROM dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
337754

SQL> update t set x=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t as of scn 337754;
X
----------
1

SQL> alter table t no flashback archive;

Table altered.

SQL> drop table t;

Table dropped.

SQL> select FLASHBACK_ARCHIVE_NAME, RETENTION_IN_DAYS,
STATUS from DBA_FLASHBACK_ARCHIVE;
FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS STATUS
---------------------- ----------------- -------
FBA01 30 DEFAULT

SQL> connect / as sysdba
Connected.
SQL> drop flashback archive fba01;

Flashback archive dropped.

SQL> drop tablespace s;

Tablespace dropped.

note that a month is 30 days. If you try to create a flashback archive in a non-empty tablespace you may get
ORA-55603: Invalid Flashback Archive command
which is not a very helpful message

2 thoughts on “flashback archive table”

  1. Ahmed Baraka

    Hi all,

    In trying the Flashback Archive feature in 11g, I got the following:
    SQL> CONN / AS SYSDBA
    Connected.

    SQL> grant FLASHBACK ARCHIVE ADMINISTER to hr ;
    Grant succeeded.

    SQL> CREATE TABLESPACE MYTBS
    2 DATAFILE ‘C:\MYTBS.DBF’ SIZE 10M ;
    Tablespace created.

    SQL> CONN HR/HR
    Connected.

    SQL> CREATE FLASHBACK ARCHIVE HR_HIST
    2 TABLESPACE USERS
    3 RETENTION 24 MONTH
    4 /
    TABLESPACE USERS
    *
    ERROR at line 2:
    ORA-55603: Invalid Flashback Archive command

    SQL> CREATE FLASHBACK ARCHIVE HR_HIST
    2 TABLESPACE “USERS”
    3 RETENTION 24 MONTH
    4 /

    Flashback archive created.

    SQL> DROP FLASHBACK ARCHIVE HR_HIST;
    Flashback archive dropped.

    SQL> CREATE FLASHBACK ARCHIVE HR_HIST
    2 TABLESPACE MYTBS
    3 RETENTION 24 MONTH
    4 /
    Flashback archive created.

    My Note is:
    ORA-55603 is returned if I try to create a flashback archive in non-empty tablespace. But the create command succeeds if you put double quot on the tablespace name (strange!)

Comments are closed.