Recycle Bin in Oracle 10G

Recycle Bin in Oracle 10G


Introduction

In our day to day work we often come across scenario when a table gets deleted from database by mistake and we end up losing the data. The only way to recourse is to use tablespace point-in-time recovery in a different database and then recreate the table in the current database using export/import or some other method. This procedure demands significant DBA effort as well as precious time, not to mention the use of a different database for cloning.

In Oracle Database 10G Flash back Table feature is introduced. It makes the revival of dropped tables as simple as executing some sql commands. This feature works similar to windows recycle bin which we use in our daily work. Let’s see how it works:

How it works

First we create a test table.

SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);

TABLE CREATED

SQL> INSERT INTO TEST VALUES (‘version 1’, sysdate);

1 ROW CREATED

SQL> SELECT * FROM TEST;

VERSION CHANGE_TI
------------- ----------------
version 1 21-AUG-07

Now let’s drop this table and see what happens.

SQL> DROP TABLE TEST;

TABLE DROPPED

Let’s check the Recycle bin.

SQL> SELECT * FROM RECYCLEBIN;

OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$Z/6jVsGtRHe46leTmSFf5g==$0 TEST DROP
TABLE USERS 2007-08-21:13:59:00
2007-08-21:14:02:42 9.0858E+12 YES YES
96232 96232 96232 32

This looks pretty weird. Let’s format the output.

SQL> SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE, CAN AS ‘UND’, CAN_PURGE AS ‘PUR’, DROPTIME FROM RECYCLEBIN;

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR
------------------------------------------- ------------------------ ---------- ----- --------
DROPTIME
---------------
BIN$Z/6jVsGtRHe46leTmSFf5g==$0 TEST TABLE YES YES
2007-08-21:14:02:42

What happened here is table TEST got renamed and it’s still present in the database. It stays in the same tablespace, with the same structure as that of the original table. If there are indexes or triggers defined on the table, they are renamed too, using the same naming convention used by the table. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table BIN$Z/6jVsGtRHe46leTmSFf5g==$0, preserving the complete object structure of the dropped table.

Recycle Bin

Recycle bin is a logical structure that catalogs the dropped objects. To see the contents of recycle bin one can use following command on Sql*Plus prompt (version 10.1)

SQL> SHOW RECYCLEBIN;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$Z/6jVsGtRHe46leTmSFf5g==$0 TABLE 2007-08-21:14:02:42

It shows the original name and recycle bin name of the dropped object.

There are two recycle bin views: USER_RECYCLEBIN and DBA_RECYCLEBIN.

USER_RECYCLEBIN: Lets users see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.

DBA_RECYCLEBIN: Lets administrators see all dropped objects in the recycle bin.

Turning recycle bin ON and OFF

Recycle bin is enabled by default in Oracle 10G. You can turn it OFF or ON from RECYCLEBIN initialization parameter, at system or session level.
To turn it OFF
• ALTER SESSION SET recyclebin = OFF;
• ALTER SYSTEM SET recyclebin = OFF;

To turn it ON
• ALTER SESSION SET recyclebin = ON;
• ALTER SYSTEM SET recyclebin = ON;

Objects already in the recycle bin are not affected by enabling or disabling the recycle bin using ALTER SYSTEM or ALTER SESSION.

The initial value of the recyclebin parameter can be set in the text initialization files init.ora:
recyclebin=on


Reinstating the Table

To reinstate the table to its original state use the following command:

SQL> FLASHBACK TABLE TEST TO BEFORE DROP;

FLASHBACK COMPLETE.

Now the table is reinstated.
SQL> SELECT * FROM TEST;

VERSION CHANGE_TI
---------- ---------
version 1 21-AUG-07

The table is reinstated. If you check the recycle bin now it will be empty.

SQL> SELECT * FROM RECYCLEBIN;

NO ROWS SELECTED.

When we drop a table with RECYCLEBIN initialisation parameter set to ON, it does not frees up the space in original tablespace. We have to purge the recycle bin to free the space:

SQL> PURGE RECYCLEBIN;

Dropped table will be deleted permanently.

If you want to drop the table permanently at one go without using the Flashback feature, you can use the following command:

SQL> DROP TABLE TEST PURGE;

It will not rename the table and will delete it permanently.
Managing the Recycle Bin
So far we have learnt that once a table or database object is dropped, it doesn’t free the space and get renamed. So, what will happen if dropped objects take up all the space?
Actually, this situation never arrives. When a tablespace is completely filled with recycle bin data such that the data files have to extend to make room for more data, the tablespace is said to be under ‘Space Pressure’. In case of ‘Space Pressure’ recycle bin data is automatically purged in a first-in-first-out manner. The dependent objects e.g. indexes are removed before the table gets purged.
If user quotas are defined for a particular table space, Space pressure situation can occur for a user even if the table space is free. In this case also recycle bin data belonging to that user is purged in FIFO manner.
We can purge the table from recylce bin using following command:
SQL> PURGE TABLE TEST;
One can also use the recycle bin name to purge it.
SQL> PURGE TABLE ‘BIN$Z/6jVsGtRHe46leTmSFf5g==$0’;
This command will delete the table permanently along with its associated objects e.g. indexes, constraints etc. If you want to delete the dependent object from recyclebin, it can be done using following command:
SQL> PURGE INEDEX IND_TEST;
If the recycle bin for a particular user in table space needs to be purged it can be done using following command:
SQL> PURGE TABLESPACE USERS USER GAURAV;
You as a DBA can purge all the objects in any tablespace using

SQL> PURGE DBA_RECYCLEBIN;
So, recyclebin can be managed in several ways depending on the requirements.
Dropped Table Versions
Consider a scenario, where a user creates and drops a table several times with same name.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
Table created.
SQL> INSERT INTO TEST VALUES ('version 1', sysdate);
1 row created.
SQL> drop table test;
Table dropped.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
Table created.
SQL> INSERT INTO TEST VALUES ('version 2', sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
Table created.
SQL> INSERT INTO TEST VALUES ('version 3', sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
So, now if we reinstate the table ‘TEST’, which table will get reinstated? Let’s check it out.

SQL> flashback table test to before drop;

Flashback complete.

SQL> select * from test;

VERSION CHANGE_TI
---------- ---------
version 3 09-OCT-07

So, we see that the version which was dropped last was reinstated first. So, recycle bin follows Last In First Out (LIFO) algorithm to reinstate the tables with same name.

You can’t reinstate the other two tables unless you rename them.

SQL> flashback table test to before drop rename to test2;
SQL> flashback table test to before drop rename to test1;


Warning while reinstating the Tables

Be warned that when you undrop a table, it gets reinstated to its original name, but its associated objects e.g. indexes, triggers are left with the recycled names. Sources such as views and procedures defined on the table are not recompiled and remain in the invalid state. Original name has to be retrieved manually and then applied to the flashback objects.

This information is stored in view named user_recylcebin. Following query should be used to retrieve the old names, before flashing - back the table:


SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'Table name ')
AND ORIGINAL_NAME! = 'Table name';

After the table is flashed-back, all the associated objects will be reinstated to their name shown in ‘Object_Name’ column.

Then you can rename them to their original names using Alter command.

Exceptions:
• Bitmap Indexes: Once they are dropped, they are not placed in recycle bin and hence not retrievable.
• Materialized View Logs: when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.
• Referential integrity constraints: that reference another table are lost when the table is put in the recyclebin and then restored.
• Constraint Names: are also not retrievable from the view.


Other uses of Flashback Tables

Recycle bin can also be used to reinstate the Table to a different point in time, replacing the current table to a different version in past. e.g. following example reinstates current table to a System Change Number (SCN) 2390874650.

Flashback table test to SCN 2390874650;

This feature uses Oracle Data Pump technology to create a different table, uses flashback to populate the table with the versions of the data at that SCN, and then replaces the original table with the new table.



Conclusion

In this document we have covered some useful features of Recylcebin in Oracle.
To Sum up:

• Recycle bin maintains the version of dropped objects and reinstate them in LIFO order.
• Flashing back a table reinstates the table to its original name, but all the associated objects have to be renamed to their original name.
• Even if the recyclebin parameter is turned off, objects already present in recyclebin can be reinstated.


References:
1. http://www.orafaq.com/node/968
2. http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html