How to Flashback Oracle Table to Before Drop Using Recycle Bin ?

 

When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin, where, in case the table was dropped in error, it can be recovered at a later time. 

This feature is called Flashback Drop, and the FLASHBACK TABLE statement is used to restore the table.

ENABLING AND DISABLING THE RECYCLE BIN

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin. When the recycle bin is disabled, dropped tables and their dependent objects are  not  placed in the recycle bin; they are just dropped, and you must use other means to recover them (such as recovering from backup).

Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin. The recycle bin is enabled by default.

You enable and disable the recycle bin by changing the recyclebin initialization parameter.

This parameter is not dynamic, so a database restart is required when you change it with an ALTER SYSTEM statement.

To disable the recycling bin:

1. Issue one of the following statements:

ALTER SESSION SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE;

2. If you used ALTER SYSTEM, restart the database.

To enable the recycling bin:

1. Issue one of the following statements:

ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE;

2. If you used ALTER SYSTEM, restart the database.

FLASHBACK TABLE TO BEFORE DROP

Onward Oracle 10g the default action of a DROP TABLE command is to logically move the table to the recycle bin by renaming it, rather than actually dropping it. The DROP TABLE … PURGE option can be used to permanently drop a table.

SQL> DROP TABLE my_table PURGE;

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 recycling bin.

Create a test table

SQL> CREATE TABLE flashback_drop_test (  id  NUMBER(10) );

SQL> INSERT INTO flashback_drop_test (id) VALUES (1);
COMMIT;

Drop the table without the PURGE keyword, and check the contents of the recycle bin using the SHOW RECYCLEBIN command.

SQL> DROP TABLE flashback_drop_test;

SQL> SHOW RECYCLEBIN;

ORIGINAL NAME    	RECYCLEBIN NAME                		 OBJECT TYPE    DROP TIME
-----------------	------------------------------ 		 ------------ 	-------------------
FLASHBACK_DROP_T 	BIN$fjdkfjdkfjkdfjkdfjkdjfkddjfkdtr==$0	 TABLE        	2024-03-29:11:09:07

A privileged user can display all recycle bins using the SHOW DBA_RECYCLEBIN command.

Restore the dropped table using the FLASHBACK TABLE command.

SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

SQL> SELECT * FROM flashback_drop_test;

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

Tables in the recycle bin can be queried like any other table. The following example drops the table again, and queries the renamed table from the recycle bin.

SQL> DROP TABLE flashback_drop_test;

SQL> SHOW RECYCLEBIN;

ORIGINAL NAME      RECYCLEBIN NAME		OBJECT TYPE	DROP TIME
----------------   -----------------		------------ 	-------------------
FLASHBACK_DROP_T   BIN$JKJlnmnmhjhvmbh==$0 	TABLE        	2024-03-29:11:18:39

SQL> SELECT * FROM " BIN$JKJlnmnmhjhvmbh==$0 ";

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

If an object is dropped and created 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.

SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;

Purge Objects From the Recycle Bin

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.

Views

In addition to the  SHOW command, you can get information about the contents of the recycle bin using the following views.

• USER_RECYCLEBIN : Displays items in the current user's recycle bin.

• DBA_RECYCLEBIN : Displays items in the recycle bin of all users in the database.

• CDB_RECYCLEBIN : When queried from the root container of a container database, it displays items in the recycle bin of all users in the containers. From any container other than the root container, it acts like the  DBA_RECYCLEBIN view.

Restrictions:-

Several restrictions apply relating to the recycling 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 recycling bin can vary. The recycle bin is cleared down if the tablespace is under space pressure. The recycle bin will not cause data files to autoextend, so in some situations objects can be purged very quickly.
• 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 are 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.