As you may know, Oracle introduced this wonderful feature called “Recycle Bin” in 10g. It behaves very much like the Windows recycle bin. when an object is dropped, it does not really get physically removed. Instead, the object still resides in the same tablespace it used to. Oracle simply renames the object and maintains a reference to renamed version.
A simple illustration should help.
Create a test table:
CREATE TABLE test1 AS SELECT * FROM all_objects WHERE rownum < 100;
Drop the test table
DROP TABLE test1;
Is it in the recycle bin?
--Synonym for user_recyclebin SELECT * FROM RECYCLEBIN;
Real table is now gone
I can no longer query the table using the original name
SELECT * FROM test1; ORA-00942: table or view does not exist
It is in the Recycle bin
However, I could still query it using the renamed name from the querying “USER_RECYCLEBIN”
SELECT * FROM "BIN$8Ufz9U/iIRzgQFQK8yUF9g==$0"
|SYS||C_OBJ#||NULL||2||2||CLUSTER||10/3/2009 9:10||10/3/2009 9:10||2009-10-03:09:10:36||VALID||N||N||N||5||NULL|
|SYS||I_OBJ#||NULL||3||3||INDEX||10/3/2009 9:10||10/3/2009 9:10||2009-10-03:09:10:36||VALID||N||N||N||4||NULL|
|SYS||TAB$||NULL||4||2||TABLE||10/3/2009 9:10||10/11/2009 9:29||2009-10-03:09:10:36||VALID||N||N||N||1||NULL|
|SYS||CLU$||NULL||5||2||TABLE||10/3/2009 9:10||10/3/2009 9:10||2009-10-03:09:10:36||VALID||N||N||N||1||NULL|
|… plus more rows|
Purge from Recycle bin
To free up the space, you could purge the table from the recycle-bin
PURGE TABLE "BIN$8Ufz9U/iIRzgQFQK8yUF9g==$0";
Purge using original table name
Although it sounds dangerous, you could have used the actual table name and the effect would be just the same
PURGE TABLE "TEST1";
Repeatedly creating/dropping table with same name
If there was a new table created with the name “test1” after the original “test1” was dropped, the above statement has no effect on the newly created table. It is simply removing the one in the recycle bin. If you constantly create and drop tables by the same name “test1”, Oracle is smart enough to keep up. Here, I created and dropped the table thrice (picture before purge was issued)
SELECT * FROM RECYCLEBIN;
Purging all tables with same original name
Purging now, purges all three versions for “TEST1” that were dropped.
PURGE TABLE "TEST1";
Restoring from Recycle bin
If you need to restore the table back from the recycle bin (assuming you have not PURGEd yet)
FLASHBACK TABLE TEST1 TO BEFORE DROP
Restoring with rename
If you have another table at this point by the same name, you might want to rename the table from recycle bin like this:
FLASHBACK TABLE TEST1 TO BEFORE DROP RENAME TO TEST2;
Restoring a specific table of multiples with same original name
If there are multiple versions of TEST1, you may use the object_name in the recycle bin to be specific.
FLASHBACK TABLE "BIN$8Ufz9U/wIRzgQFQK8yUF9g==$0" TO BEFORE DROP RENAME TO TEST3
On a down side, objects can start accumulating in the recycle bin unless the DBA cleans them up on a scheduled basis. If you know for a fact that you will not need the dropped version of the object, you can skip the recycle bin
Bypassing Recycle bin for DROP’s
This bypasses the recycle bin at DROP time:
DROP TABLE test1 PURGE;
Bypassing Recycle bin for DROP’s at session level
You could even bypass the recycle bin as operations are performed at the session level
ALTER SESSION SET recyclebin = OFF;
Different flavors of PURGE!
Purging the recycle bin comes in different flavors depending on how much or how little you want to purge and you would pick the right one depending on the permissions you have.
|PURGE TABLE [table_name];||Purges all dropped versions of the table from recycle bin|
|PURGE TABLESPACE [tablespace_name];||Purges all objects in given tablespace_name from recycle bin|
|PURGE TABLESPACE [tablespace_name] USER [user];||Purges objects from a given tablespace_name for user|
|PURGE RECYCLEBIN;||Purges all objects for the current user from recycle bin|
|PURGE DBA_RECYCLEBIN;||Purges everything there is in the recycle bin|
As you code, please remember to use the PURGE keyword if you are positive that you will not need the “DROPped” object again.