Oracle Recycle Bin

Flashback

In computing world, a concept of recycle bin or trash is not new. A recycle bin, or trash can, is temporary storage for files that have been deleted by the user, but not yet permanently erased by the operating system from the disk drive. Apple first implemented a trash can icon during the development of Lisa. The concept was carried over to the Apple Macintosh, and has existed and proliferated since then.

Although MS-DOS operating systems and some third parties utilities did offer UNDELETE feature for DOS and Windows users, Microsoft didn’t offer an icon similar to the trash can until it introduced a Recycle Bin icon in Windows 95. In fact, this concept of trash was subject to a lawsuit which Apple eventually lost.

Oracle Database Recycle Bin

And, Oracle waited until Oracle10g to introduce a recycle bin concept. Better late than never! Oracle Recycle Bin is actually a data dictionary table containing information about dropped objects. When you drop a table in Oracle, the database does not immediately remove/purge the table from the database. It is just a logical delete. The table is not permanently removed from the database. Instead, it renames the table and places it and any dependant objects such as indexes, constraints and triggers in a recycle bin.

Oops!

In the life of a DBA or an application developer, there are "Oops" moments that they love to hate or hate to love for a long time. Many of us might have accidentally dropped a table that very well be an "Oops" moment. For example,

DROP TABLE US_STATES;

Thanks!

Recycle Bin could very well be a job-saver for “Oops” drops! It has been a life-saver for many who had dropped some important database tables either accidentally and/or unconsciously. You can view the recycle bin by:

JMEHTA > show recyclebin ;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
US_STATES BIN$OozJnFttRLa3AntqEdXy/g==$0 TABLE 2009-07-07:10:42:58

What a relief! US_STATES table is peacefully resting in Recycle Bin! Although Oracle has renamed this dropped table with a system generated name that begins with BIN$.

You can continue to access data from the dropped table through its recycled name, as shown below:

JMEHTA > select * from "BIN$OozJnFttRLa3AntqEdXy/g==$0" ;

ID CODE NAME
---------- -- ------------------------------
1 VA Virginia
2 MD Maryland

But what about indexes, triggers and grants that were associated with this table? They are also recoverable.

“SELECT * FROM USER_RECYCLEBIN” or “SELECT * FROM RECYCLEBIN” yields the following output.

Note: RECYCLEBIN or USER_RECYCLEBIN are synonyms and can be used interchangeably.

JMEHTA > select OBJECT_NAME, ORIGINAL_NAME, TYPE from user_recyclebin ;

OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ -------------------------- -------------------------
BIN$fIgPImIyQsi9yL3c3F8+8g==$0 US_STATES_PK INDEX
BIN$s4VUcdg2SDuHuOVLq3Gyyg==$0 US_STATES_BRI_SEQ TRIGGER
BIN$OozJnFttRLa3AntqEdXy/g==$0 US_STATES TABLE

If you are a DBA, you can also view database level recycle bin by:

JMEHTA> select * from dba_recyclebin

Bingo!

You can recover a table and its dependant objects through flashback command, as shown below.

JMEHTA > flashback table us_states to before drop ;

Let's verify table recovery.

JMEHTA > SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'US_STATES' ;

TABLE_NAME
------------------------------
US_STATES

What!

You may shout “What!” when you realize that Oracle didn’t rename associated indexes and triggers to their original names.

JMEHTA > select index_name from user_indexes where table_name = 'US_STATES' ;

INDEX_NAME
------------------------------
BIN$leIeukwWQL6zfEoR7XKk5g==$0

JMEHTA > select trigger_name from user_triggers where table_name = 'US_STATES' ;

TRIGGER_NAME
------------------------------
BIN$43f91QgqSBe3gG055leeVA==$0

You can rename indexes and triggers as shown below:

JMEHTA > alter index "BIN$leIeukwWQL6zfEoR7XKk5g==$0" rename to US_STATES_PK ;

Index altered.

JMEHTA > alter trigger "BIN$43f91QgqSBe3gG055leeVA==$0" rename to US_STATES_BRI_SEQ ;

Trigger altered.

Managing Recycle Bin

Oracle provides various commands to manage recycle bin.

To Purge a single table from Recycle Bin:

PURGE TABLE TABLE_NAME

To empty current user's recycle bin and release all space associated with objects in the recycle bin:

PURGE RECYCLEBIN or PURGE USER_RECYCLEBIN

To Empty Recycle Bin at Database Level:

PURGE DBA_RECYCLEBIN
SYSDBA privilege is required to purge DBA_RECYCLEBIN

Bypassing Recycle Bin

Specify PURGE if you want to drop the table and release the space associated with it in a single step. If you specify PURGE, then the database does not place the table and its dependent objects into the recycle bin. Using this clause is equivalent to first dropping the table and then purging it from the recycle bin. This clause lets you save one step in the process.

DROP TABLE TABLE_NAME PURGE;

To disable Recycle Bin:

ALTER SESSION SET recyclebin = OFF; (Session Level)

ALTER SYSTEM SET recyclebin = OFF; (system level)

recyclebin=off (system level init.ora parameter)

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).

To enable Recycle Bin

ALTER SESSION SET recyclebin = ON; (Session Level)

ALTER SYSTEM SET recyclebin = ON; (system level)

recyclebin=on (system level init.ora parameter)

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin. The recycle bin is enabled by default.

Space Pressures

Dropped objects continue to stay in the recycle bin until the owner of the dropped objects decides to permanently remove them using the PURGE command. Objects in the Recycle Bin will be automatically purged by the database if

• A user creates a new table or adds data that causes their quota to be exceeded.
• The tablespace needs to extend its file size to accommodate create/insert operations.
• Corresponging tablespace or user is dropped

Misc.

Please note individual index drops or trigger drops are not recoverable through recycle bin. For example, if you drop an index or a trigger, then they are not placed in recycle bin. But if you drop a table, all dependant objects such as indexes and triggers are placed in recycle bin, and are recoverable.


Happy Recycling! Go Green!