Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle Recycle Bin Tips

Oracle Database Tips by Donald BurlesonConsulting

Oracle Recycle Bin

Oracle 10g introduced the recycle bin. You can recover a table that you have dropped from the Oracle recycle bin by using the flashback table command as seen here:

SQL> DROP TABLE books;

SQL> FLASHBACK TABLE books TO BEFORE DROP;

The recycle bin uses the flashback table command.

 However, the more time that has passed since the table was dropped, the less likely it will be in the Oracle recycle bin (The Oracle recycle bin is purged periodically based on a number of different criteria).

The contents of the Oracle recycle bin can be viewed from SQL*Plus by using the show recyclebin command:

SQL> show recyclebin;
 
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ ------------
BOOKS            BIN$D3XWKKUCQVq2EG8/vkjNDw==$0 TABLE        2005-05-30

Managing the Recycle Bin

The recycle binis a new feature in Oracle 10g which keeps dropped objects. When you drop an object, no space is released and the object is moved to the logical container called the recycle bin. In case you want to get the table back, issue the flashback drop command as it was explained in the previous scenarios. Each user has a view called recycle_bin which he can use to get the list of dropped objects.

You can query the dropped object by not restoring it from the recycle bin. This is done by using the special name that was given to the dropped object by Oracle, i.e. the object name starting with bin$.

To get the name of all dropped objects, use the show recycle_bin command. More detailed information can be found by querying the user_recyclebin view. To understand the concept, see the following example:

SQL>
create
 table tbl_rc_bin (id number);
Table created. 

SQL>
drop
 table tbl_rc_bin;
Table dropped. 

SQL>
show
 recyclebin; 

ORIGINAL NAME    RECYCLEBIN NAME        OBJECTTYPE  DROP TIME
------------- --------------------      ----------- -------
TBL_RC_BIN    BIN$fzdTKcxkrMDgQAB/AQAUbA==$0 TABLE  2015-02-09:22:06:47
 
SQL>
select
 object_name, original_name, operation, type, droptime
from
 user_recyclebin;
 
OBJECT_NAME              ORIGINAL_NAME   OPERATION  TYPE    DROPTIME
------------             --------------- ----------- ----  -------
BIN$fzdTKcxkrMDgQAB/AQAUbA==$0 TBL_RC_BIN   DROP TABLE       2015-02-
9:22:06:47

Note: When running queries for used space and free space in a tablespace, segments that have moved to the recyclebin will not be listed as normal table/index segments consuming used space, but will reduce the free space.  So be aware of the size of the recyclebin when generating space usage reports for the database.

Recycle bin objects, i.e. dropped objects, will not be included during the Oracle export. Only the real objects can be exported. So after importing, there is no need to panic when you find that the total number of objects count is different from source to target.

Purging Objects From the Recycle Bin

To remote the tables and indexes from the recycle binand free the space that they consume, use the PURGE clause.  Also see here, how to scheduled a purge of the recycle bin.

To purge the specific table or index, use:

SQL>
purge
 table tbl_rc_bin;
Table purged.
SQL>
purge user_recyclebin;
Recyclebin purged.

To purge the objects in the user's recycle bin, use:

SQL>
purge
 recyclebin;
Recyclebin purged.

To purge all objects from the recycle bin, use:

SQL>
conn / as
 sysdba
Connected.
SQL>
purge
 dba_recyclebin;
DBA Recyclebin purged.
SQL>

To purge all objects of the specific tablespace, use:

SQL>
purge
 tablespace users;
Tablespace purged.
SQL>

Disabling the Recycle Bin Functionality

There is a recyclebin parametern the parameter file whose default is ON.

SQL>
show
 parameter recyclebin;
 
NAME                       TYPE        VALUE
-------------------------- ----------- -----------------------------
recyclebin                 string      ON
SQL>

To disable it to function, use:

alter system set recyclebin=off;

To disable it in the session level, use:

alter session set recyclebin=off;

To delete the table without putting it in the recycle bin, use the purge command at the end of the DROP TABLE clauseas follows:

SQL>
create
 table tbl_rc (id number);
Table created. 

SQL>
drop
 table tbl_rc purge;
Table dropped.

SQL>
show
 recyclebin;
SQL>

Although this is enabled by default, if the parameter recyclebin is set to OFF at the instance level, dropped tables are not retained in the recycle bin.  Similarly, if the tablespace has low free space, older dropped tables are silently purged from the recycle bin.  So it is advisable to query the recycle bin immediately after the problem is identified.  Take care to ensure that a recycle bin is available before running your tests for flashback query on a dropped table or flashback table to before drop.


Purging Objects in the Oracle Recycle Bin

If you decide that you are never going to restore an item from the recycle bin, you can use the PURGE statement to remove the items and their associated objects from the recycle bin and release their storage space. You need the same privileges as if you were dropping the item.

PURGE TABLE BIN$jsleilx392mk2=293$0;

You can achieve the same result with the following statement:

PURGE TABLE int_admin_emp;

For more information on Oracle recycle bin, see my notes below:

RECYCLE BIN view tips

Oracle Concepts - Dropping Tables - Oracle Recycle Bin

Purge Recyclebin

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.