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.
Let’s see how Flashback Table Works. Let’s first create a test table named “TESTFLASH” and insert some data into it.
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.
1. Let’s see the table in the present schema.
SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------ ------- ---------- TESTFLASH TABLE
2. Now, we accidentally drop the table:
SQL> drop table TESTFLASH; Table dropped.
3. 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.
4. Let’s see the table in the present schema.
SQL> SELECT * FROM TAB; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- TESTFLASH TABLE
5. The table is reinstated effortlessly. If you check the recycle bin now, it will be empty.
SQL> Show Recyclebin SQL>