Oracle Recycle Bin

Recycle bin in Oracle

Recycle bin was introduced in Oracle 10g

Recycle bin is actually a data dictionary table containing information about dropped objects. When an object has been dropped from a locally managed tablespace (LMTS), which is not the SYSTEM tablespace, the database does not immediately delete the object & reclaim the space associated with the object. Instead, it places the object and any dependent objects in the recycle bin, which is similar to deleting a file/folder from Windows/Macintosh. You can then restore the object, its data and its dependent objects from the recycle bin.

The FLASHBACK DROP and the FLASHBACK TABLE feature places the object in the recycle bin after removing the object from the database. This eliminates the need to perform a point-in-time recovery operation.

When objects are dropped, the objects are not moved from the tablespace they were in earlier; they still occupy the space there. The recycle bin is merely a logical structure that catalogs the dropped objects.

The recyclebin is enabled, by default, from Oracle 10g.
But you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.
SQL> ALTER SYSTEM/SESSION SET RECYCLEBIN=ON/OFF SCOPE=BOTH;
SQL> SHOW PARAMETER RECYCLEBIN

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin.

SQL> DROP TABLE attachment;
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$Wk/N7nbuC2DgRAAAd7F0UA==$0 TABLE


The deleted table has been renamed with system name, physically it’s not dropped.

The renaming convention is as follows:
BIN$unique_id$version
where:

• unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases
• version is a version number assigned by the database

Use the following command to see recycle bin contents:
SQL> SELECT * FROM RECYCLEBIN;
or
SQL> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------- ------------------------ ---------- -----------------
ATTACHMENT BIN$Wk/N7nbuC2DgRAAAd7F0UA==$0 TABLE 2008-10-28:11:46:55

This shows the original name of the table, as well as the new name in the bin.

Note that users can see only objects that they own in the recycle bin.

Remember, placing tables in the recycle bin does not free up space in the original tablespace. To free the space, you need to purge the bin using:
SQL> PURGE RECYCLEBIN;

But what if you want to drop the table completely, without needing a flashback feature, in that case, you can drop it permanently using:
SQL> DROP TABLE table-name PURGE;

This is similar to SHIFT+DELETE in Windows. This command will not rename the table to the recycle bin name; rather, it will be deleted permanently, as it would have been before Oracle 10g.

To get back the deleted table and its contents
SQL> FLASHBACK TABLE table-name/bin-name TO BEFORE DROP [RENAME TO new-name];

You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin.
SQL> SELECT * FROM "BIN$W1PPyhVRSbuv6g+V69OgRQ==$0";

Managing Recycle Bin in Oracle

If the tables are not really dropped in this process, therefore not releasing the tablespace, what happens when the dropped objects take up all of that space?
When a tablespace is completely filled up with recycle bin data such that the datafiles have to extend to make room for more data, the tablespace is said to be under "space pressure." In that scenario, objects are automatically purged from the recycle bin in a first-in-first-out manner. The dependent objects (such as indexes) are removed before a table is removed.

Similarly, space pressure can occur with user quotas as defined for a particular tablespace. The tablespace may have enough free space, but the user may be running out of his or her allotted portion of it. In such situations, Oracle automatically purges objects belonging to that user in that tablespace.

In addition, there are several ways you can manually control the recycle bin. If you want to purge the specific table from the recycle bin after its drop, you could issue
SQL> PURGE TABLE table-name;

or using its recycle bin name

SQL> PURGE TABLE "BIN$Wk/N7nbuC2DgRCBAd7F0UA==$0";

This command will remove table and all dependent objects such as indexes, constraints, and so on from the recycle bin, saving some space.

If you want to permanently drop an index from the recycle bin, you can do so using:
SQL> PURGE INDEX index-name;

This will remove the index only, leaving the copy of the table in the recycle bin. Sometimes it might be useful to purge at a higher level. For instance, you may want to purge all the objects in recycle bin in a tablespace.
You would issue:
SQL> PURGE TABLESPACE tablespace-name;

You may want to purge only the recycle bin for a particular user in that tablespace. This approach could come in handy in data warehouse environments where users create and drop many transient tables. You could modify the command above to limit the purge to a specific user only:
SQL> PURGE TABLESPACE tablespace-name USER user-name;

The PURGE TABLESPACE command only removes recyclebin segments belonging to the currently connected user. Therefore, it may not remove all the recyclebin segments in the tablespace. You can determine which users have recyclebin segments in a target tablespace using the following query:
SQL> SELECT DISTINCT owner FROM dba_recyclebin WHERE ts_name = "tablespace-name";

You can then use the above PURGE TABLESPACE command to purge the segments for each of the users.

A normal user, such as SCOTT, could clear his own recycle bin with
SQL> PURGE RECYCLEBIN;

A DBA can purge all the objects in any tablespace using
SQL> PURGE DBA_RECYCLEBIN;

The PURGE DBA_RECYCLEBIN command can be used only if you have SYSDBA system privileges. It removes all objects from the recycle bin, regardless of user.

Note: When a table is retrieved from the recycle bin, all the dependent objects for the table that are in the recycle bin are retrieved with it. They cannot be retrieved separately.

The un-drop feature brings the table back to its original name, but not the associated objects like indexes and triggers, which are left with the recycled names. Sources such as views and procedures defined on the table are not recompiled and remain in an invalid state. These old names must be retrieved/renamed manually and then applied to the flashed-back table.

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 recycle bin, it purges indexes first.

The constraint names are also not retrievable from the view. They have to be renamed from other sources.

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace.
The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

• When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.
• When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.
• When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.

When the recycle bin is disabled, dropped tables and their dependent objects are not placed in the recycle bin; they are just dropped, and you must use other means to recover them (such as recovering from backup). Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin.

Related Oracle Recycle bin Views

RECYCLEBIN$        (base table)

DBA_RECYCLEBIN

USER_RECYCLEBIN
RECYCLEBIN          (synonym for USER_RECYCLEBIN)

Related Oracle Articles: FLASHBACK  Flashback Query in Oracle  FRA (Flash/Fast Recovery Area) in Oracle