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:
Purge
Recyclebin
and include the URL for the page.
