Oracle Recycle Bin

The Recycle Bin, as its name implies, is the storage of deleted items. In principle, it is a data dictionary table that places the user's deletion (drop) out of the database object information. Objects that are deleted by the user are not deleted by the database and will still occupy space. Unless the user is manually purge or because the storage space is not enough to be erased by the database. Database has such a function, can reduce a lot of unnecessary trouble.

If a table is deleted, objects associated with the table, such as indexes, constraints, and other dependent objects, are prefixed with bin$$.

To view the Recycle Bin status:

The return result is on, or the Recycle Bin is open.

The return result is off, or the Recycle Bin is closed.

SELECT  from WHERE = ' RecycleBin '

On the system and session-level Recycle Bin, turn off.

--Open the system-level recycle BinALTERSYSTEMSETRecycleBin=  on; --Open the session-level recycle BinALTERSESSIONSETRecycleBin=  on; --turn off the system-level recycle BinALTERSYSTEMSETRecycleBin= OFF; --close the session-level recycle BinALTERSESSIONSETRecycleBin= OFF;

To view the contents of the Recycle Bin:

SELECT *  from RecycleBin;        SELECT *  from User_recyclebin;        SELECT *  from

To recover objects in the Recycle Bin:

TABLE << Dropped_table_name>>toDROPto<<new_table_name> >

Note: The rename here is to rename the deleted object, which is an optional command.

Delete Recycle Bin contents:

1. Delete a table

TABLE << table_name>>;

2. Deleting an index

INDEX << Index_name>>

3. Delete Table Space related objects

PURGE tablespace<<table_name>>

4. Delete specific user table space related objects

PURGE tablespace<<table_name>>USER<<user_name >>

5. Empty the Recycle Bin

6. Delete the table completely

DROP TABLE << TABLE_NAME>>

Oracle Recycle Bin