Flashback Drop (Recycle Bin)

 

Starting Oracle 10g the default action of a DROP TABLE command is to move the table to the recycle bin (or rename it), rather than actually dropping it. The PURGE option can be used to permanently drop a table.

The recycle bin is a logical collection of previously dropped objects, with access tied to the DROP privilege. The contents of the recycle bin can be shown using the SHOW RECYCLEBIN command and purged using the PURGE TABLE command. As a result, a previously dropped table can be recovered from the recycle bin:

CREATE TABLE flashback_drop_test ( id  NUMBER(10));

INSERT INTO flashback_drop_test (id) VALUES (1);

COMMIT;

DROP TABLE flashback_drop_test;

SHOW RECYCLEBIN

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE        2004-03-29:11:09:07

EST

FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

SELECT * FROM flashback_drop_test;

        ID

----------

         1

Tables in the recycle bin can be queried like any other table:

DROP TABLE flashback_drop_test;

SHOW RECYCLEBIN

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

FLASHBACK_DROP_T BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0 TABLE        2004-03-29:11:18:39

EST

SELECT * FROM "BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0";

        ID

----------

         1

If an object is dropped and recreated multiple times all dropped versions will be kept in the recycle bin, subject to space. Where multiple versions are present it's best to reference the tables via the RECYCLEBIN_NAME. For any references to the ORIGINAL_NAME it is assumed the most recent object is drop version in the referenced question. During the flashback operation the table can be renamed like:

FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;

Several purge options exist:

PURGE TABLE tablename;                   -- Specific table.

PURGE INDEX indexname;                   -- Specific index.

PURGE TABLESPACE ts_name;                -- All tables in a specific tablespace.

PURGE TABLESPACE ts_name USER username;  -- All tables in a specific tablespace for a specific user.

PURGE RECYCLEBIN;                        -- The current users entire recycle bin.

PURGE DBA_RECYCLEBIN;                    -- The whole recycle bin.

Several restrictions apply relating to the recycle bin:

• Only available for non-system, locally managed tablespaces.
• There is no fixed size for the recycle bin. The time an object remains in the recycle bin can vary.
• The objects in the recycle bin are restricted to query operations only (no DDL or DML).
• Flashback query operations must reference the recycle bin name.
• Tables and all dependent objects are placed into, recovered and purged from the recycle bin at the same time.
• Tables with Fine Grained Access policies aer not protected by the recycle bin.
• Partitioned index-organized tables are not protected by the recycle bin.
• The recycle bin does not preserve referential integrity.

SQL> DROP TABLE t;

 

Table dropped.

 

SQL> exec print_table( q'[SELECT * FROM recyclebin WHERE original_name = 'T']' );

 

OBJECT_NAME                   : BIN$KGpN+vdaIL/gRAADukiYGw==$0

ORIGINAL_NAME                 : T

OPERATION                     : DROP

TYPE                          : TABLE

TS_NAME                       : USERS

CREATETIME                    : 2004-06-05:11:50:04

DROPTIME                      : 2004-06-05:14:29:16

DROPSCN                       : 1434683

PARTITION_NAME                :

CAN_UNDROP                    : YES

CAN_PURGE                     : YES

RELATED                       : 53427

BASE_OBJECT                   : 53427

PURGE_OBJECT                  : 53427

SPACE                         : 256

-----------------

 

PL/SQL procedure successfully completed.

 

 

FLASHBACK TABLE TO A TIME IN THE PAST.

Firstly enable row movement for the table.In this example the table name is TEST.

SQL> ALTER TABLE TEST ENABLE ROW MOVEMENT;

TIME : 08:00:00

SQL> select * from test;

SALARY
----------
5000

TIME :08:00:01

SQL> update test set salary =6000;

1 row updated.

SQL> select * from test;

SALARY
----------
6000

SQL> commit;

Commit complete.

Now flashback table to time 08:00:00

SQL> FLASHBACK TABLE TEST to timestamp TO_TIMESTAMP( '2005-09-13 08:00:00','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL> SELECT * FROM TEST;

SALARY
----------
5000