Flashback Table Feature in Oracle Database 10g [ID 265254.1]

Flashback Table Feature in Oracle Database 10g [ID 265254.1]

Modified 25-JUL-2010     Type HOWTO     Status PUBLISHED

***Checked for relevance on 25-Jul-2010***

PURPOSE
=======
This bulletin illustrates the new Flashback Table feature provided with the 10g 
database

SCOPE & APPLICATION
===================
Can be used by Oracle Support Analyst and DBA

Flashback Query concept was introduced in Oracle9i. But it can't flash back DDL
operations such as dropping a table. Flashback Table feature in Oracle 10g makes
the revival of a dropped table as easy as the execution of few statements.

Lets see how Flashback Table Works.....


SQL> Create table TESTFLASH (id number);

SQL> Insert into TESTFLASH values (1);
SQL> Insert into TESTFLASH values (2);
SQL> Insert into TESTFLASH values (3);
SQL> Insert into TESTFLASH values (4);
SQL> Insert into TESTFLASH values (5);
SQL> Commit;

SQL> select * from TESTFLASH;

        ID
----------
         1
         2
         3
         4
         5
         

5 rows selected.


Let's see the table in the present schema.
========================================== 

SQL> select * from tab;

TNAME                    TABTYPE  CLUSTERID
------------------------ ------- ----------
TESTFLASH                TABLE


Now, we accidentally drop the table:
====================================

SQL> drop table TESTFLASH;

Table dropped.


Let's check the status of the table now. 
========================================

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE


The table TESTFLASH is gone but note the presence of the new table 
BIN$04LhcpndanfgMAAAAAANPw==$0. The dropped table TESTFLASH, instead of 
completely disappearing, was renamed to a system-defined name. It stays in the
same tablespace, with the same structure as that of the original table. If there
are indexes or triggers defined on the table, they are renamed too, using the 
same naming convention used by the table. Any dependent sources such as 
procedures are invalidated; the triggers and indexes of the original table are 
instead placed on the renamed table BIN$04LhcpndanfgMAAAAAANPw==$0, preserving 
the complete object structure of the dropped table. 

The table and its associated objects are placed in a logical container known as
the "RECYCLE BIN," which is similar to the one in your PC. However, the objects
are not moved from the tablespace they were in earlier; they still occupy the 
space there. The RECYCLE BIN is merely a logical structure that catalogs the 
dropped objects. Use the following command from the SQL*Plus prompt to see its 
content (you'll need SQL*Plus 10.1 to do this): 

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ ------------------
TESTFLASH        BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE        2004-02-16:21:13:31


This shows the original name of the table, TESTFLASH, as well as the new name in
the recycle bin (BIN$04LhcpndanfgMAAAAAANPw==$0). Use the FLASHBACK TABLE 
command to reinstate the table.


SQL> FLASHBACK TABLE TESTFLASH TO BEFORE DROP;

FLASHBACK COMPLETE.

Now query the TESTFLASH table:

SQL> select * from TESTFLASH;

        ID
----------
         1
         2
         3
         4
         5
         

5 rows selected.


Let's see the table in the present schema.
========================================== 

SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TESTFLASH                      TABLE


The table is reinstated effortlessly. If you check the recycle bin now, it will be empty.

SQL> Show Recyclebin
SQL>

      

Related Products Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition