Recycle Bin Tips

Oracle Tips by Burleson

Oracle Flashback Drop

Prior to Oracle , a DROP command permanently removed objects from the database. In Oracle , a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until you purge the object. You can restore the object from the recycle bin at any time.

This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.

Recycle Bin

A recycle bin contains all the dropped database objects until,

You permanently drop them with the PURGE command.
Recover the dropped objects with the UNDROP command.
There is no room in the tablespace for new rows or updates to existing rows.
The tablespace needs to be extended.

You can view the dropped objects in the recycle bin from two dictionary views:

user_recyclebin – lists all dropped user objects
dba_recyclebin – lists all dropped system-wide objects
Example 1: Dropping an Object

In the example below, the name of the object is changed when it is dropped and moved to the recycle bin. The recycle bin also keeps the original name of the object. This feature allows you to create a new object of the same name and then drop it again.

SQL> create table test (col_a varchar(4));

Table created.

SQL> select object_name, original_name, type
     from   user_recyclebin;

no rows selected

SQL> drop table test;

Table dropped.

SQL> select object_name, original_name, type
     from   user_recyclebin;

OBJECT_NAME           ORIGINAL_NAME      TYPE
--------------------- ----------------   ------------------
RB$$42513$TABLE$0     TEST               TABLE

SQL> create table test (col_b varchar(4));

Table created.

SQL> select object_name, original_name, type
     from   user_recyclebin;

OBJECT_NAME           ORIGINAL_NAME      TYPE
--------------------- ----------------   ------------------RB$$42513$TABLE$0     TEST               TABLE

SQL> drop table test;

Table dropped.

SQL> select object_name, original_name, type from user_recyclebin;

Example 1: Dropping an Object

OBJECT_NAME              ORIGINAL_NAME      TYPE
------------------------ ----------------   ------------------
RB$$42513$TABLE$0        TEST               TABLE
RB$$42514$TABLE$0        TEST               TABLE

Example 2: Restoring a Dropped Object

This example will restore a dropped table test.

SQL> flashback table RB$$42514$TABLE$0 to before drop;

Flashback complete.

Example 3: Dropping a Table Permanently

This statement puts the table in the recycle bin:

SQL> drop table purge test;

This statement removes the table permanently:

SQL> purge table RB$$42514$TABLE$0;
Table purged.

Example 4: Dropping a Tablespace

You can only issue this command when the tablespace users is empty. Objects in the recycle bin of tablespace users will be purged:

SQL> drop tablespace users;

When you issue this command, objects in the tablespace users are dropped. They are not placed in the recycle bin. Any objects in the recycle bin belonging to the tablespace users are purged.

SQL> drop tablespace users including contents;

Example 5: Purging the Recycle Bin

This statement purges the user recycle bin:

SQL> purge recyclebin;

Recyclebin purged.

This statement removes all objects from the recycle bin:

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Oracle Example 5: Purging the Recycle Bin

This statement purges all objects from tablespace users in the recycle bin:

SQL> purge tablespace users;

Tablespace purged.