|
Category :
Database
Resources -> Database Administration
DB Version
:
Oracle 10G
OS Details :
Sun Solaris9
Reference
: Oracle Metalink
The
10G version of oracle has introduced a new feature called the Recycle
Bin. The Recycle Bin is a virtual container where all dropped objects
reside. Underneath the covers, the objects are occupying the same
space as when they were created. For example, if table DEPT was
created in the TESTTBS tablespace, the dropped table DEPT remains in
the TESTTBS 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$$.
(Case 1) In the
event of you requiring to recover the table from the recycle bin then
do the following:
-- Drop the objects without the PURGE option
SQL> Drop table dept;
-- This will show you the details of the recycle bin
SQL> show recyclebin
-- Recover the dropped table using this command:
SQL> FLASHBACK TABLE DEPT TO BEFORE DROP;
-- This will now show nothing in it since the objects in the recycle
bin were recovered
SQL> Show Recyclebin
(Case 2) You may decide to not have oracle retain the
object to the recycle bin. You can do this by combining the object
drop and the recycle bin purge in one statement by doing this:
-- connect as the table owner
SQL> DROP TABLE DEPT PURGE;
Table dropped.
SQL> SELECT * FROM recyclebin;
no rows selected
(Case 3) In 10G release 1, the recycle bin feature can
be altogether disabled by doing this:
-- Make changes to the instance and the spfile
SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;
-- If your instance doesn't use the spfile feature then include this
entry in the init file:
_recyclebin=false
In 10G release 2, the recycle bin feature can be disabled at the
session or the system level by doing this:
-- Make changes at the session level
SQL> ALTER SESSION SET recyclebin = OFF;
-- Make changes to the instance and the spfile
SQL> ALTER SYSTEM SET recyclebin = OFF SCOPE=BOTH;
-- If your instance doesn't use the spfile feature then include this
entry in the init file:
recyclebin = OFF
|
