Oracle – Recycle Bin Basics

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;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
BIN$8Ufz9U/iIRzgQFQK8yUF9g==$0 TEST1 DROP TABLE USERS 2014-01-31:11:48:53 2014-01-31:11:49:09 8.79742E+12 NULL YES YES 62559619 62559619 62559619 64

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"
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
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;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
BIN$8Ufz9U/iIRzgQFQK8yUF9g==$0 TEST1 DROP TABLE USERS 2014-01-31:11:48:53 2014-01-31:11:49:09 8.79742E+12
BIN$8Ufz9U/wIRzgQFQK8yUF9g==$0 TEST1 DROP TABLE USERS 2014-01-31:11:59:18 2014-01-31:11:59:19 8.79742E+12 NULL YES YES 62559621 62559621 62559621 64
BIN$8Ufz9U/pIRzgQFQK8yUF9g==$0 TEST1 DROP TABLE USERS 2014-01-31:11:59:06 2014-01-31:11:59:07 8.79742E+12 NULL YES YES 62559620 62559620 62559620 64

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

Suggestion

As you code, please remember to use the PURGE keyword if you are positive that you will not need the “DROPped” object again.

References:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s