Flashback table feature on Oracle 11g

FLASHBACK TABLE statement is used to restore an earlier state of a table in the  event of human or application error.Though It entirely depends  on the amount of undo data that is
present in the system.Also we cant flashback a table to earlier stage in case of any ddl operation that changes the table structure.flashback on is not required in order to do
the flashback table.
You cannot 'flashback table to before drop' a table which has been created in the SYSTEM tablespace.

[oracle@server1 ~]$ sqlplus / as sysdba
SQL> create user soumya identified by soumya default tablespace users;

User created.
[oracle@server1 ~]$ conn soumya/soumya
SQL> create table test ( id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.
SQL> select * from test;

        ID
----------
         1
         1
         1

SQL> drop table test;

Table dropped.


SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Ig9YoNA/E4/gUKjAZgINCg==$0 TABLE        2015-10-14:16:23:47

SQL> flashback table test to before drop;

Flashback complete.

SQL> select * from test;

        ID
----------
         1
         1
         1

Now lets try to flashback a table which resides in system tablespace.
SQL> show user
USER is "SYS"
SQL> create table flash (id number);    

Table created.

SQL> insert into flash values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dba_recyclebin;

no rows selected

SQL> show recyclebin;
SQL>

SQL> flashback table flash to before drop;
flashback table flash to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

So, if a table resides in system tablesapce and if its dropped it doesnt stay in recylebin, rather its being dropped permamnently from the database.

To query a dropped table:-
SQL> drop table test;

Table dropped.
SQL>  show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Ig9YoNBAE4/gUKjAZgINCg==$0 TABLE        2015-10-14:16:37:02
While querying the recycle bin, make sure the system generated table name is enclosed in double quotes, else it will throw error.


SQL> select * from BIN$Ig9YoNBAE4/gUKjAZgINCg==$0 ;

SQL> select * from "BIN$Ig9YoNBCE4/gUKjAZgINCg==$0" ;
        ID
----------
         1
         1
         1


Now lets try to insert some data inside the dropped table which is inside recyclebin.
SQL> insert into "BIN$Ig9YoNBEE4/gUKjAZgINCg==$0" values(2);
insert into "BIN$Ig9YoNBEE4/gUKjAZgINCg==$0" values(2)
            *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin


So we can not perform DDL/DML over objects in Recycle Bin.

Flashback a table in the past to a specific point in time:-
SQL> set time on
16:52:51 SQL>
16:52:52 SQL>  alter table test enable row movement ;

Table altered.

16:53:21 SQL> select * from test;

        ID
----------
         1
         1
         1
         2

16:53:37 SQL>
16:53:44 SQL> update test set id=100 where id=1 ;

3 rows updated.

16:54:00 SQL> commit;

Commit complete.

16:54:04 SQL> select * from test;

        ID
----------
       100
       100
       100
         2

Now lets flashback the table
17:25:05 SQL> FLASHBACK TABLE TEST to timestamp TO_TIMESTAMP( '2015-10-14 16:54:01' ,'YYYY-MM-DD HH24:MI:SS');

Flashback complete.

17:25:10 SQL>  select * from test;

        ID
----------
         1
         1
         1
         2

17:25:17 SQL>



To rename an object while flashing back from recyclebin:-
17:37:39 SQL> create table test11(id number);

Table created.

17:37:58 SQL>  insert into test11 values (1);

1 row created.

17:38:05 SQL> commit;

Commit complete.

17:38:09 SQL> drop table test11;

Table dropped.

17:39:21 SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST11           BIN$Ig9YoNBIE4/gUKjAZgINCg==$0 TABLE        2015-10-14:17:39:21

17:39:57 SQL> flashback table "BIN$Ig9YoNBIE4/gUKjAZgINCg==$0" to before drop rename to test12 ;
Flashback complete.

17:40:11 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST12                       TABLE

17:50:18 SQL> select * from test12;

        ID
----------
         1




Please share your ideas and opinions about this topic.

If you like this post, then please share it with others.
Please subscribe on email for every updates on mail.