Oracle recycle bin

There exits two recycle bins USER_RECYCLEBIN  the same as RECYCLEBIN and DBA_RECYCLEBIN.

PREPARING DEMO…

–Create a test table

CREATE TABLE testtb (testcol NUMBER);

–Insert row

INSERT  INTO testtb VALUES(1);

COMMIT;

–Drop it

DROP TABLE  testtb;

–Query recycle bin

SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME 

FROM   RECYCLEBIN;

–Output

OBJECT_NAME                     | ORIGINAL_NAME | DROPTIME

----------------------------------------------------------------------

BIN$nNV1TgjzTeeusBT1fqxeyw==$0  | TESTTB        | 2011-04-05:11:14:46

As you can see Oracle renames table to a system generated name which starts with BIN$.

PURGING….DELETING

This table is not actually dropped, it is just renamed and is still taking the place in the tablespace.

To relieve the space there is several ways:

–Globally clear the recycle bin

PURGE DBA_RECYCLEBIN;

–Clear recycle bin by tablespace

PURGE TABLESPACE users;

–Clear recycle bin in you schema

 PURGE RECYCLEBIN;

or

 USER_RECYCLEBIN;

–Purge individual object

PURGE TABLE "BIN$nNV1TgjzTeeusBT1fqxeyw==$0"

IMPORTANT NOTE!!!

In one of the Oracle tests, there is the question which says that you cannot purge object from the recycle bin by specifying its original name.

Like that:

PURGE TABLE testtb

BUT it works!!!

–Check it

SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME 

FROM   RECYCLEBIN;

–Output

OBJECT_NAME  |  ORIGINAL_NAME | DROPTIME 

Sometimes Oracle purges objects itself from recycle bin in the following cases:

* When user hits its quota limit on the tablespace
      At that point oracle purges objects from the recycle bin using FIFO method. So purges the oldest one until it’s enough.

* Before a datafile is autoextended .

SELECTING…

I am using table which is created in DEMO section.

–Query

SELECT * FROM "BIN$nNV1TgjzTeeusBT1fqxeyw==$0"; 

–Output

TESTCOL 

1

FLASHBACKING…RECOVERING

FLASHBACK TABLE testtb TO BEFORE DROP

–Query

SELECT * FROM  testtb

–Output

TESTCOL 

1 

Now let’s do the following:

–Create a test table

CREATE TABLE testtb (testcol NUMBER);

–Drop it

DROP TABLE  testtb;

–Check the recycle bin

SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME 
FROM   RECYCLEBIN;

–Output

OBJECT_NAME                    | ORIGINAL_NAME | DROPTIME

--------------------------------------------------------------------

BIN$wkk5qTDzS7OsaI+LHOVv6g==$0 | TESTTB        | 2011-04-05:14:07:08

Again repeat these steps:

–Create a test table

CREATE TABLE testtb (testcol NUMBER);

–Drop it

DROP TABLE  testtb;

–Check the recycle bin

SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME 

FROM   RECYCLEBIN;

–Output

OBJECT_NAME                   | ORIGINAL_NAME | DROPTIME 

-------------------------------------------------------------------

BIN$wkk5qTDzS7OsaI+LHOVv6g==$0| TESTTB        | 2011-04-05:14:07:08 

BIN$ZdI0ZO98R8mLymiv96CALA==$0| TESTTB        | 2011-04-05:14:07:12

If you flashback the TESTTB table , the most resent one will be flashbacked. Let’s check it:

–Flashback table

FLASHBACK TABLE testtb TO BEFORE DROP

–Check the recycle bin

SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME 

FROM   RECYCLEBIN;

–Output

OBJECT_NAME                    | ORIGINAL_NAME | DROPTIME 

--------------------------------------------------------------------

BIN$wkk5qTDzS7OsaI+LHOVv6g==$0 | TESTTB        | 2011-04-05:14:07:08

Look at the DROPTIME, it shows that the oldest entry was left in the recycle bin.

But if you want to flashback that one, not the current one, do it:

Assume that we have not flashbacked any table and we have the following entries in the recycle bin:

OBJECT_NAME                   | ORIGINAL_NAME | DROPTIME 

------------------------------------------------------------------

BIN$wkk5qTDzS7OsaI+LHOVv6g==$0| TESTTB        | 2011-04-05:14:07:08 

BIN$ZdI0ZO98R8mLymiv96CALA==$0| TESTTB        | 2011-04-05:14:07:12

And now we want to flashback the table which was dropped for the first time.

FLASHBACK TABLE "BIN$wkk5qTDzS7OsaI+LHOVv6g==$0" TO BEFORE DROP

–Check the recycle bin

SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME

FROM   RECYCLEBIN;

–Output

OBJECT_NAME                    | ORIGINAL_NAME | DROPTIME 

--------------------------------------------------------------------

BIN$ZdI0ZO98R8mLymiv96CALA==$0 | TESTTB        | 2011-04-05:14:07:12

But if the table by this name already exist, then you will need to rename it before flashbacking:

FLASHBACK TABLE "BIN$wkk5qTDzS7OsaI+LHOVv6g==$0" 

TO BEFORE DROP RENAME TO testtb1

When the table is dropped and placed into the recycle bin the following objects that refers to that table also are placed in the recycle bin:

* Indexes

* LOG segments

* Triggers etc…

BUT the following objects are not placed into the recycle bin:

* Bitmap join indexes

* Materialized view logs

* Referential integrity constraints

So when you flashback the table the following objects such as bitmap join indexes, materialized view logs, referential integrity constraints will not be recovered.

If you drop index before dropping a table, this index will not be recovered during flashbacking the table. The same is with other objects that should be located into the recycle bin.

DISABLING RECYCLING…

The initialization parameter RECYCLEBIN identifies if the dropped object should be placed into the recycle bin or not.

The default value is ON, means that it should.

You can disable it at the system or session level.

ALTER SESSION SET RECYCLEBIN=OFF

After that no object will be placed into the recycle bin, consequently they can’t be flashbacked. If there were objects into the recycle bin before setting RECYCLEBIN  parameter to OFF you are able to flashback them.

There is another way to delete object without locating it into the recycle bin:

DROP TABLE testtb;

;

!!!IMPORTANT

Recycle bin exists for the tables only in non-SYSTEM, locally managed tablespaces. 

So you can’t flashback the table from recycle bin in SYS schema, because this schema  is located in SYSTEM tablespace and doesn’t have any recycle bin.