Using Oracle's recycle bin

Natalka Roshak's picture
articles: 

One of the many new features that Oracle 10g introduced is the recyclebin. When enabled, this feature works a little bit like the familiar Windows recycle bin or Mac Trash. Dropped tables go "into" the recyclebin, and can be restored from the recyclebin. OraFAQ has already published an article covering the basics; in this article, I'll cover some of the more subtle aspects of the recyclebin.

THE BASICS

First, a quick review of the basics. There are two recyclebin views: USER_RECYCLEBIN and DBA_RECYCLEBIN. For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN. The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.

When the recyclebin is enabled, any tables that you drop do not actually get deleted. Instead, when you drop a table, Oracle just renames the table and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.

For example, consider this simple table:

SQL> create table tst (col varchar2(10), row_chng_dt date);

Table created.

SQL> insert into tst values ('Version1', sysdate);

1 row created.

SQL> select * from tst ;

COL        ROW_CHNG
---------- --------
Version1   16:10:03

If the RECYCLEBIN initialization parameter is set to ON (the default in 10g), then dropping this table will place it in the recyclebin:

SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
  2  from recyclebin 
SQL> /

OBJECT_NAME                    ORIGINAL_NAME TYPE  UND PUR DROPTIME
------------------------------ ------------- ----- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:10:12

All that happened to the table when we dropped it was that it got renamed. The table data is still there and can be queried just like a normal table:

SQL> alter session set nls_date_format='HH24:MI:SS' ;

Session altered.

SQL> select * from "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;

COL        ROW_CHNG
---------- --------
Version1   16:10:03

Since the table data is still there, it's very easy to "undrop" the table. This operation is known as a "flashback drop". The command is FLASHBACK TABLE... TO BEFORE DROP, and it simply renames the BIN$... table to its original name:

SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst ;

COL        ROW_CHNG
---------- --------
Version1   16:10:03

SQL> select * from recyclebin ;

no rows selected

It's important to know that after you've dropped a table, it has only been renamed; the table segments are still sitting there in your tablespace, unchanged, taking up space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it, or by purging it from the recyclebin.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
  2  from recyclebin
SQL> /

OBJECT_NAME                    ORIGINAL_NAME TYPE                      UND PUR DROPTIME
------------------------------ ------------- ------------------------- --- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE                     YES YES 2006-09-01:16:10:12

SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;

Table purged.

SQL> select * from recyclebin ;

no rows selected

You have several purge options. You can also purge everything from the USER_RECYCLEBIN using PURGE RECYCLEBIN; a user with DBA privileges can purge everything from all recyclebins using DBA_RECYCLEBIN; and finally, you can purge recyclebin objects by schema and user with PURGE TABLESPACE USER .

Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space, or until you hit your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.

DROPPED TABLE VERSIONS

Just as you can wind up with several versions of a file with the same name in the Windows recycle bin, you can wind up with several versions of a table in the Oracle recyclebin. For example, if we create and drop the TST table twice, we'll have two versions in the recyclebin:

SQL> create table tst (col varchar2(10), row_chng_dt date);

Table created.

SQL> insert into tst values ('Version1', sysdate);

1 row created.

SQL> drop table tst;

Table dropped.

SQL> create table tst (col varchar2(10), row_chng_dt date);

Table created.

SQL> insert into tst values ('Version2', sysdate);

1 row created.

SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
  2  from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME TYPE  UND PUR DROPTIME
------------------------------ ------------- ----- --- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:10:12
BIN$HGnc55/8rRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:19:53

Query the two dropped tables to verify that they are different:

SQL> select * from "BIN$HGnc55/7rRPgQPeM/qQoRw==$0";

COL        ROW_CHNG
---------- --------
Version1   16:10:03

SQL> select * from "BIN$HGnc55/8rRPgQPeM/qQoRw==$0" ;

COL        ROW_CHNG
---------- --------
Version2   16:19:45

If we issue a FLASHBACK DROP command for TST, which version will Oracle restore?

SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst;

COL        ROW_CHNG
---------- --------
Version2   16:19:45

Oracle always restores the most recent version of the dropped object. To restore the earlier version of the table, instead of the later one, we can either keep flashing back until we hit the version we want, or we can simply refer to the correct version of the table by using its new BIN$... name. For example, dropping TST once more gives us two versions in the recyclebin again:

SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
  2  from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME TYPE   UND PUR DROPTIME
------------------------------ ------------- ------ --- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE  YES YES 2006-09-01:16:10:12
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST           TABLE  YES YES 2006-09-01:16:21:00

To flashback to the first version, refer to the BIN$... name of the first version of TST:

SQL> flashback table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" to before drop;

Flashback complete.

SQL> select * from tst;

COL        ROW_CHNG
---------- --------
Version1   16:10:03

The second version is still hanging out in the recyclebin:

SQL> select object_name, original_name, operation, can_undrop as "UND", can_purge as "PUR", droptime
  2  from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME  OPERATION UND PUR DROPTIME
------------------------------ -------------- --------- --- --- -------------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST            DROP      YES YES 2006-09-01:16:21:00

DEPENDENT OBJECTS

In a modern relational database, few tables stand alone. Most will have indexes, constraints, and/or triggers. Dropping a table also drops these dependent objects. When you drop a table with the recyclebin enabled, the table and its dependent objects get renamed, but still have the same structure as before. The triggers and indexes get modified to point to the new BIN$ table name. (Any stored procedures that referenced the original object, though, are invalidated.) For example:

SQL> truncate table tst;

Table truncated.

SQL> insert into tst values ('Version3', sysdate);

1 row created.

SQL> create index ind_tst_col on tst(col);

Index created.

SQL> select * from tst;

COL        ROW_CHNG
---------- --------
Version3   16:26:10

SQL> drop table tst ;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
  2  from recyclebin
  3  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME  TYPE   UND PUR DROPTIME
------------------------------ -------------- ------ --- --- -------------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST            TABLE  YES YES 2006-09-01:16:21:00
BIN$HGnc55//rRPgQPeM/qQoRw==$0 TST            TABLE  YES YES 2006-09-01:16:27:36
BIN$HGnc55/+rRPgQPeM/qQoRw==$0 IND_TST_COL    INDEX  NO  YES 2006-09-01:16:27:36

The RECYCLEBIN views have a few other columns that make the relationship between TST and IND_TST_COL clear:

SQL> select object_name, original_name, type, can_undrop as "UND", 
  2  can_purge as "PUR", droptime, base_object, purge_object
  3  from recyclebin
  4  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME   TYPE  UND PUR DROPTIME            BASE_OBJECT   PURGE_OBJECT
------------------------------ --------------- ----- --- --- ------------------- -----------   ------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST             TABLE  YES YES 2006-09-01:16:21:00 233032        233032
BIN$HGnc55//rRPgQPeM/qQoRw==$0 TST             TABLE  YES YES 2006-09-01:16:27:36 233031        233031
BIN$HGnc55/+rRPgQPeM/qQoRw==$0 IND_TST_COL     INDEX  NO  YES 2006-09-01:16:27:36 233031        233434

The PURGE_OBJECT column is the object number of the object itself; eg. the object number of IND_TST_COL is 233434. Note the value of the BASE_OBJECT column for IND_TST_COL: 233031, the object number of the associated version of the TST table.

If we FLASHBACK DROP the TST table, its index will be restored - but Oracle will not rename it to its original name. It will retain its BIN$.. name:

SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst ;

COL        ROW_CHNG
---------- --------
Version3   16:26:10

SQL> select index_name from user_indexes where table_name='TST' ;

INDEX_NAME
------------------------------
BIN$HGnc55/+rRPgQPeM/qQoRw==$0

I'm not sure why Oracle bothers storing the index's original name, since it doesn't seem to be used for anything. If we now drop this copy of the TST table, Oracle doesn't "remember" that the original name of the index "BIN$HGnc55/+rRPgQPeM/qQoRw==$0"was IND_TST_COL - the ORIGINAL_NAME column in RECYCLEBIN holds the ugly string "BIN$HGnc55/+rRPgQPeM/qQoRw==$0" :

SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", 
  2  droptime, base_object, purge_object
  3  from recyclebin
  4  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME   TYPE  UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT
------------------------------ --------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST             TABLE YES YES 2006-09-01:16:21:00      233032       233032
BIN$HGnc56ABrRPgQPeM/qQoRw==$0 TST             TABLE YES YES 2006-09-01:16:31:43      233031       233031
BIN$HGnc56AArRPgQPeM/qQoRw==$1 BIN$HGnc55/+rRP INDEX NO  YES 2006-09-01:16:31:43      233031       233434
                               gQPeM/qQoRw==$0

Note the values in the CAN_UNDROP and CAN_PURGE columns for the index (displayed as "UND" and "PUR" above). An index cannot be undropped without the table - so CAN_UNDROP is set to NO. It can, however, be purged without purging the table:

SQL> purge index "BIN$HGnc56AArRPgQPeM/qQoRw==$1" ;

Index purged.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", 
  2  droptime, base_object, purge_object
  3  from recyclebin
  4  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME  TYPE  UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT
------------------------------ -------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST            TABLE YES YES 2006-09-01:16:21:00      233032       233032
BIN$HGnc56ABrRPgQPeM/qQoRw==$0 TST            TABLE YES YES 2006-09-01:16:31:43      233031       233031

Now, if we restore the table, it will be restored without the index:

SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst ;

COL        ROW_CHNG
---------- --------
Version3   16:26:10

SQL> select index_name from user_indexes where table_name='TST' ;

no rows selected

If you drop a table with associated LOB segments, they are handled in a similar way, except that they cannot be independently purged: CAN_UNDROP and CAN_PURGE are set to NO, and they are purged if you purge the table from the recyclebin, restored with the table if you restore it.

LIMITATIONS

A few types of dependent objects are not handled like the simple index above.

  • Bitmap join indexes are not put in the recyclebin when their base table is DROPped, and not retrieved when the table is restored with FLASHBACK DROP.
  • The same goes for 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.

If space limitations force Oracle to start purging objects from the recyclebin, it purges indexes first. If you FLASHBACK DROP a table whose associated indexes have already been purged, it will be restored without the indexes.

DISABLING THE RECYCLEBIN

In Windows, you can choose to permanently delete a file instead of sending it to the recycle bin. Similarly, you can choose to drop a table permanently, bypassing the Oracle recyclebin, by using the PURGE clause in your DROP TABLE statement.

SQL> purge recyclebin;

Recyclebin purged.

SQL> select * from recyclebin;

no rows selected

SQL> create table my_new_table (dummy varchar2(1));

Table created.

SQL> drop table my_new_table purge;

Table dropped.

SQL> select * from recyclebin;

no rows selected

If you disable the recyclebin at the session level, with ALTER SESSION SET RECYCLEBIN=OFF, it has the same effect as putting PURGE at the end of all your drop statements. Note, however, that you can still use FLASHBACK DROP to restore objects that were put in the recyclebin before you set RECYCLEBIN=OFF. For example:

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",
  2   droptime, base_object, purge_object
  3  from recyclebin
  4  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME TYPE  UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT
------------------------------ ------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc56ACrRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:34:12      233031       233031

SQL> alter session set recyclebin=off ;

Session altered.

SQL> create table tst (col varchar2(10), row_chng_dt date);

Table created.

SQL> insert into tst values ('Version5', sysdate);

1 row created.

SQL> drop table tst ;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",
  2   droptime, base_object, purge_object
  3  from recyclebin
  4  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME   TYPE UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT
------------------------------ -------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc56ACrRPgQPeM/qQoRw==$0 TST            TABLE YES YES 2006-09-01:16:34:12      233031       233031

SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst ;

COL        ROW_CHNG
---------- --------
Version3   16:26:10

CONCLUSION

This article has explored some of the subtler ramifications of the recyclebin. To sum up:

-The recyclebin may contain several versions of a dropped object. Oracle restores them in LIFO order; you can restore older versions by repeatedly restoring until you get the version you want, or by using the correct version's BIN$... name directly.
- Oracle drops most dependent objects along with the table, and restores them when the table is restored with FLASHBACK DROP, but does not restore their names. You can purge dependent objects separately to restore the table without them.
- Even after turning RECYCLEBIN OFF, you can FLASHBACK DROP objects that were already in the RECYCLEBIN.

About the author

Natalka Roshak is a senior Oracle and Sybase database administrator, analyst, and architect. She is based in Kingston, Ontario, and consults across North America. More of her scripts and tips can be found in her online DBA toolkit at http://toolkit.rdbms-insight.com/.

Comments

Hi there,

This articel is really useful to resolve the space issues in our new migration (7.3.4 to 10g) project.

Many thanks.

Best Regards,
Vijaya Bhaskar Utla.

Hi Natalka Roshak,

You are simply great. The article is very useful and explained yhe topic very well. This feature has been understood simply in just one reading. You give very good examples, which helped to understand the feature.

Thanks a lot for this useful information (and for sure, in a very simple manner).

Regards,
Sachin

Its gr8

mam can u post us main differnce between 9i and 10g
so that we can compare easily

Nice to see a reply again

From
Sundar

Sundar, that's a pretty big request - there are a lot of new features. I suggest you check out the 10g New Features guide. Here's a link to the 10gR2 version: Oracle® Database New Features Guide, 10g Release 2 (10.2). Arup Nanda also did a great feature on OTN, Oracle Database 10g: The Top 20 Features for DBAs.

If i drop a user type, it is not in recyclebin

Dear Natalka Roshak,

The article is very useful and explained the topic very well. This feature has been understood simply in just one reading. You give very good examples, which helped to understand the feature.
I was looking for this BIN tables which contained in this table ALL_TAB_PARTITIONS in sys schema. Which failed the table partition creation automatically.
Thanks a lot for this useful information (and for sure, in a very simple manner).

Regards,
Jojan Tharamangalathil
+436-9911605913

Hi

This is really good article to understand recyclebin concept in oracle. I really thankful to you for your great Efforts.

Thanks

Yawar Rizvi

Dear Natalka Roshak,
it is very nice and Easy to Learn & Understandable
Article. without previous knowledge any person can
understand.
thank u for such a nice article.

Regards,
Vinit Kumar Singh

Examples are good that made this article more easy to understand by a novice like me. Thanks a lot.

Nothing more to add to this comment as the basic points are well covered by Natalka Roshak

- Gopal

Hi Natalka,

really nice article.. One question

My database have lots of indexes, sequences and dependencies between tables. My application connects database using JDBC API.
I deleted some tables and those went in recycle bin.
But the real problem is these deleted tables are still referenced by my application.
In my application, I am caching all tables meta data. But I surprised even after deletion of those tables why they are available in the metadata.

is there any way when I select metadata, filter only the active tables ?

Milind

Hi Milind,

If you don't want your application to reference deleted tables, you may either purge the recyclebin or drop the table using the following syntax:

drop table "table_name" purge;

Note that the tables dropped with purge option are not recoverable from recyclebin as they are purged from it.

Hi Natalka,
thanks for posting this informative article. I am looking for an option to purge (drop) certain partitions of a table for good (without those going into the recycle bin). I was wondering if an option exists to do precisely that.

Thank you for your time!
Amit

Since the bin objects are still there, they will appear in selects from user_tables, user_triggers, etc.
So, if you do schema manipulation/verification scripts, you should filter them out by hand in your queries.

I had eg. a script that verified sync of max value of table columns to the sequence that was used (via trigger) to simulate autoincrement. If a difference was found, the script would update the sequence nextval.
It first scanned user tables for triggers with conforming syntax to the "autoincrement standard", then looked up the relative sequence name in the trigger body via a regexp. (This was thought to be more error proof than determining the sequence name via a base prefix added to the table name)

Now, the name of the sequence is part of the trigger body, so when moved to the recycled bin the trigger body is not altered and it still refers to the live objects.

In the end my script found deleted triggers on deleted tables, and updated the sequence current values of real sequences to reflect the max id present in the deleted tables. And without any kind of error anywhere - the logs indicated the good sequences being updated - it took a while to debug!

In 10g Recyclebin is one of the features from where the Recyclebin takes the space is it from the space allocated i.e from the SGA or the space from the Hard Disk. When a tablespace is dropped will it be in the Recyclebin

it is a just logical structure

Hello mam,

It is a very nice article.
I have dropped one table which is associated with bitmap join indexes and bitmap indexes.
so how to restore that bitmap join indexes and bitmap indexes.
Kindly help me for tha same....

Thanks,
Illiyaz.

Can a table still be recovered via flashback if the recycle bin is disabled?

rameshwor's picture

This is very good explanation regarding Recyblebin concept in Oracle.

Really wonderful article very simply adn nicely explained.
Thanks for such an article.
Partha

Thank you all. It is a very resourcefull article.

We have a before drop trigger at database level to prevent dropping the objects. Recently we had a issue where the application was trying to create a table and insert some clob rows into it. It happened internally try to purge the recycle bin objects to make some space and oracle did not report any ORA- error since it was this trigger preventing it. we had hard time trying to find out the root cause.

Can you think of any options where the db level before triggers internally does not apply to recycle bin. I tried to exclude the BIN$ objects in the triggers but it looks like there are some objects without BIN$ in the recyclebin which is same as original name.

select OWNER,OBJECT_NAME,ORIGINAL_NAME,TYPE from dba_recyclebin where OBJECT_NAME not like '%BIN$%';

OWNER OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ ---------------------------------------- ---------------------------------------- -------------------------
TEST SYS_IL0009235834C00026$$ SYS_IL0009235834C00026$$ LOB INDEX
TEST SYS_LOB0009235834C00026$$ SYS_LOB0009235834C00026$$ LOB

Recycle bin concept demystified! Thanks a ton!

Hi,

This is an excellent post. Thank you for that.

I've recovered my tables from the recycle bin, but indexes now retain their BIN$... names. How do I restore their original names?

Thanks in advanced.

Can we also find out which user or IP has use this drop command on server database?
Recently we faced same problem.
we recovered our data but unable to find who did that?
Can we...?

Nice article!