|
THE RECYCLE BIN in Oracle 10g
Oracle has introduced "Recycle Bin" Oracle 10g where all dropped objects are
stored. If you drop a table in Oracle 10g then any associated objects to this
table such as indexes, constraints and other dependant objects are simply
renamed with a prefix of BIN$$. Underneath the covers, the objects are occupying
the same space as when they were created.
Example:
If table TEST1 was created in the USERS tablespace, the dropped table TEST1
remains in the USERS tablespace. Dropped tables and any associated objects such
as indexes, constraints, nested tables, and other dependant objects are not
moved, they are simply renamed with a prefix of BIN$$.
SQL> create table test1 ( a number);
Table created.
SQL> drop table test1;
Table dropped.
SQL> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------
-------------------
TEST1 BIN$Ik84R6rKEj7gRAgAIMR0GQ==$0 TABLE 2006-11-15:14:34:13
SQL>
|
You can continue to access the data in a dropped table. Each user has the same
rights and privileges on Recycle Bin objects before it was dropped. You can view
your dropped tables by querying the new RECYCLEBIN view.
How to clean recyclebin?
You can user purge recycle bin command to delete all the objects from recycle
bin.
SQL> PURGE RECYCLEBIN;
Recyclebin purged.
SQL> SHOW RECYCLEBIN |
What if I want to delete objects permanently?
In order to completely remove table from the DB and to release the space, you
can use new PURGE command.
SQL> purge table test1;
Table purged.
In this case, table test1 will be deleted from the database permanently.
Space/Quota Issue?
Objects in the Recycle Bin will remain in the database until the owner of the
dropped objects decides to permanently remove them using the new PURGE command.
The Recycle Bin objects are counted against a user's quota. But Flashback Drop
is a non-intrusive feature. Objects in the Recycle Bin will be automatically
purged by the space reclamation process if
1. A user creates a new table or adds data that causes his/her quota to be
exceeded.
2. The tablespace needs to extend its file size to accommodate create/insert
operations.
Can I disable Recycle Bin?
Yes, you can disable it at your session level by using following command.
SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;
The dropped objects, when recyclebin was ON will remain in the recyclebin even
if we set the recyclebin parameter to OFF.
| 
