Job Seekers   Employers
dbapool: Authors | Submissions | Contact Us
   Forgot password? | Sign up
  Home   Discussion Forum   Articles   Interview Questions   FAQs   Scripts   Rewards   Analyzer   White Papers   Blog   Certification   Downloads   Tools
   
THE RECYCLE BIN in Oracle 10g




By Vigyan Kaushik
Nov 15, 2006

Digg! digg!     Print    email to friend Email to Friend

Note: This article was written for educational purpose only. Please refer to the related vendor documentation for detail.




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.



 

 
About author:

Vigyan Kaushik is an Oracle certified professional serving IT industry for more than 12 years as an Oracle DBA and System Administrator. He has expertise in Database Designing, Administration, Networking, Tuning, Implementation, Maintenance with web deployment activities on different Unix flavors as well as on Windows Operating Systems.

 




  About Us Advertise Terms of Use Privacy Newsletters Contact Us    

Home   Discussion Forum   FAQs  Articles  Jobs   Newsletters  Directory  Downloads 

Our Premium Sponsor