|
|
Flashback Table Feature in Oracle Database 10g
|
hi,everyone
Flashback Query concept was introduced in Oracle9i. But it can't flash back DDLoperations such as dropping a table. Flashback Table feature in Oracle 10g makesthe 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>
|
