Recyclebin

Flashback drop table is one of the awesome feature in oracle10g. Flashback query feature was introduced in oracle9i and we were able to read the past data by using timestamp or SCN for existing table in database. Oracle9i flashback query does not have the ability to recover the dropped tables. But oracle10g introduced a flashback drop feature to recover the dropped tables.

Oracle10g introduced recyclebin to keep the dropped tables for longer time subject to space.

When recyclebin is enabled, any table you drop will not get dropped. Instead, it will rename the dropped tables and dependent object to system generated name that starts with BIN$.

Recyclebin is a logical structure within each tablespace that holds dropped tables and dependent object to that table. Dependent objects are index, triggers, constraints, LOB segments, nested_tables etc. The space associated with the dropped table is not immediately available, but shows up the DBA_FREE_SPACE. Free space in the tablespace that is not occupied by dropped tables. When space pressure occurs in the tablespace, objects in the recyclebin are deleted in First-in first-out(FIFO) fashion.

The dropped object still belongs to the owner and still counts againts the quota for the owner in the tablespace.

As long as a tablespace has no space pressure, dropped objects are available indefinitely for recovery. Dropped objects are removed automatically when there is a space pressure. Also dropped objects can be removed manually by PURGE command.

This article is tested on Oracle10g version.

Let us test the recyclebin features as multiple scenario.
Scenario 1

Here table is created with dependent objects(index, primary key, trigger, constraints, LOB segments, nested_tables etc). Let us drop the table and recover using recyclebin. When we recover this table, the dependent objects are also recovered. But the dependent object names never changed to original names.

scott@orcl> create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> alter table flashdrop
2 add primary key(table_name);

Table altered.

scott@orcl> create index idxx on flashdrop(tablespace_name);

Index created.

scott@orcl> create trigger trg_flashdrop
2 before insert on flashdrop
3 for each row
4 begin
5 null;
6 end;
7 /

Trigger created.

scott@orcl> select count(*) from flashdrop;

COUNT(*)
----------
5

scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHDROP BIN$jmJpbe5kSqOynybZkA+yfg==$0 TABLE 2009-05-04:18:37:20


scott@orcl> select object_name,original_name from recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$WcPD/4MlRLayVzQi3x/Vzw==$0 SYS_C005982
BIN$gXTgHi0nQPidAxbnCx5b+w==$0 TRG_FLASHDROP
BIN$jmJpbe5kSqOynybZkA+yfg==$0 FLASHDROP
BIN$4sBW2ND1RYmDYs3JerZodA==$0 IDXX

scott@orcl> flashback table flashdrop to before drop;

Flashback complete.

scott@orcl> select count(*) from flashdrop;

COUNT(*)
----------
5

scott@orcl> show recyclebin

scott@orcl> select object_name,original_name from recyclebin;

no rows selected

scott@orcl> select constraint_name from
2 user_constraints where table_name='FLASHDROP';

CONSTRAINT_NAME
------------------------------
BIN$OIrKaH31R+WaqLWxgMWBQw==$0
BIN$lWD5jjLRQjacvAJJN6i0pQ==$0

scott@orcl> select trigger_name from user_triggers
2 where table_name='FLASHDROP';

TRIGGER_NAME
------------------------------
BIN$gXTgHi0nQPidAxbnCx5b+w==$0

scott@orcl> select index_name from user_indexes
2 where table_name='FLASHDROP';

INDEX_NAME
------------------------------
BIN$WcPD/4MlRLayVzQi3x/Vzw==$0
BIN$4sBW2ND1RYmDYs3JerZodA==$0

Scenario 2

Tables can be dropped and recovered with different name.

scott@orcl>create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> select count(*) from flashdrop;

COUNT(*)
----------
5

scott@orcl>

scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl> flashback table flashdrop to before drop
2 rename to old_flashdrop;

Flashback complete.

scott@orcl> select count(*) from flashdrop;
select count(*) from flashdrop
*
ERROR at line 1:
ORA-00942: table or view does not exist

scott@orcl> select count(*) from old_flashdrop;

COUNT(*)
----------
5

Scenario 3

The recyclebin may contain several versions of a dropped object. Oracle restores them in LIFO order. It restores most recent version of dropped object. We can restore older versions by repeatedly restoring until you get the version you want, or by using the correct version's BIN$... name directly.

scott@orcl>create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl>create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl>create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl> select object_name, original_name,droptime
2 from recyclebin;

OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ -------------------------------- -------------------
BIN$QZwwUzhJQT2GZM/PIEW3pA==$0 FLASHDROP 2009-05-05:10:06:00
BIN$GR72mxnyQp+++qxOmVGQfg==$0 FLASHDROP 2009-05-05:10:05:25
BIN$ePj5BAIxTuuZy4oRO3d/oA==$0 FLASHDROP 2009-05-05:10:05:36

scott@orcl>

scott@orcl> flashback table flashdrop to before drop;

Flashback complete.

scott@orcl> select object_name, original_name,droptime
2 from recyclebin;

OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ -------------------------------- -------------------
BIN$GR72mxnyQp+++qxOmVGQfg==$0 FLASHDROP 2009-05-05:10:05:25
BIN$ePj5BAIxTuuZy4oRO3d/oA==$0 FLASHDROP 2009-05-05:10:05:36

scott@orcl>

In this scenario, Oracle recovered the table which is dropped recently.

Scenario 4

Recyclebin feature is enabled by default in oracle10g. But after turning the recyclebin feature off, we can recover the tables which are already existing in recyclebin.

scott@orcl>create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHDROP BIN$WXrd4IW+SKmYh9EGU8bg5Q==$0 TABLE 2009-05-05:10:24:59

scott@orcl> ALTER SESSION SET recyclebin = OFF;

Session altered.

scott@orcl> flashback table flashdrop to before drop;

Flashback complete.

scott@orcl> show recyclebin;

scott@orcl> select count(*) from flashdrop;

COUNT(*)
----------
5

scott@orcl>

Scenario 5

Let us say, we start updating on flashdrop table at 12PM. We complete all the updates around 2PM. After we realized that, the updates went wrong and we want to revert the table back to 12PM... We can achieve this using FLASHBACK TABLE feature.

scott@orcl>create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> SELECT current_scn from v$database;

CURRENT_SCN
-----------
19220251

scott@orcl> delete flashdrop where rownum < 2;

scott@orcl> commit;

Commit complete.

scott@orcl> update flashdrop set object_name=lower(object_name);

4 rows updated.

scott@orcl> select object_name from flashdrop;

OBJECT_NAME
--------------------------------------------------------------------------------
audit_seq
bin$ekjojm2as66hxzkrauejia==$0
bin$hfdops9utc2qdmy9pamg6w==$0
bin$idl5ashfqmqe8ctrwu8k8q==$0

scott@orcl> flashback table flashdrop to scn 19220251;
flashback table flashdrop to scn 19220251
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


scott@orcl> alter table flashdrop enable row movement;

Table altered.

scott@orcl> flashback table flashdrop to scn 19220251;

Flashback complete.

scott@orcl> select object_name from flashdrop;

OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
BIN$EkjOJm2AS66HXzkrAueJiA==$0
BIN$HFDoPs9uTC2QdMY9pAMG6w==$0
BIN$IDl5ashFQMqE8ctrWU8K8Q==$0

scott@orcl>

Flashback of table can also be performed using timestamps as below...

FLASHBACK TABLE flashdrop TO TIMESTAMP TO_TIMESTAMP('2004-05-06 10:00:00', 'YYYY-MM-DD HH:MI:SS');


How do we purge objects manully in recyclebin?

PURGE TABLE tablename; -- Specific table.
PURGE INDEX indexname; -- Specific index.
PURGE TABLESPACE ts_name; -- All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username; -- All tables in a specific tablespace for a specific user.
PURGE RECYCLEBIN; -- The current users entire recycle bin.
PURGE DBA_RECYCLEBIN; -- The whole recycle bin.

How do we bypass the recyclebin?

We can add PURGE statement with drop table command to bypass the recyclebin. The tables will be dropped without going into recycle bin. But when we drop tablespace(DROP TABLESPACE .... INCLUDING CONTENTS), tablespace is not placed in recyclebin. It drops associated recyclebin for that tablespace.

scott@orcl> DROP TABLE EMPTABLE PURGE;

Table dropped.

scott@orcl> FLASHBACK table emptable to before drop;
FLASHBACK table emptable to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

scott@orcl>

Naming convention for object in recyclebin:

When we drop the object, the dropped objects are renamed and moved to recyclebin. The names are globally unique and are used to identify the objects while they are in the recycle bin. The recyclebin name of an object is always 30 characters long. Dropped Object names are formed as follows: BIN$$globalUID$version

globalUID is a globally unique, 24 character long identifier generated for the object.
version is a version number assigned by the database.

Limitations on Recyclebin:

1. Only non system locally managed tablespace(LMT) can have a recycle bin. However, dependent objects in a dictionary managed tablespace are protected if the dropped object is LMT.

2. All dependent objects will be placed in recyclebin except bitmap join index, FK constraints, Materialized view logs)

3. Indexes are protected only if the table is dropped first. Explicitly dropping an index does not place in recyclebin.

4. There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.

5. Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.

6. Partitioned index-organized tables are not protected by the recycle bin.

Recyclebin Views:

We have two views, USER_RECYCLEBIN, DBA_RECYCLEBIN. For our convenience, synonym RECYCLEBIN is created which is pointing to user_recyclebin.

How do we disable the recyclebin?

The recyclebin feature is enabled in oracle10g by default. We can change the initialization parameter RECYCLEBIN to enable or disable this feature.

ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;