The recyclebin is a public synonym and it is based on the view user_recyclebin which in turn is based on sys.recyclebin$ table.
SQl> Select object_type,object_name from dba_objects where object_name='RECYCLEBIN';
OBJECT_TYPE OBJECT_NAME
-----------------------------------------------
SYNONYM RECYCLEBIN
SQL> desc recyclebin
Name Null? Type
----------------------------------------- -------- ------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER
Related recyclebin objects:
SQL> SELECT SUBSTR(object_name,1,50),object_type,owner
FROM dba_objects
WHERE object_name LIKE '%RECYCLEBIN%';
/
SUBSTR(OBJECT_NAME,1,50) OBJECT_TYPE OWNER
--------------------------- ------------------- ------------ ----------
RECYCLEBIN$ TABLE SYS
RECYCLEBIN$_OBJ INDEX SYS
RECYCLEBIN$_TS INDEX SYS
RECYCLEBIN$_OWNER INDEX SYS
USER_RECYCLEBIN VIEW SYS
USER_RECYCLEBIN SYNONYM PUBLIC
RECYCLEBIN SYNONYM PUBLIC
DBA_RECYCLEBIN VIEW SYS
DBA_RECYCLEBIN SYNONYM PUBLIC
9 rows selected.
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. If table EMP was created in the USERS tablespace, the dropped table EMP 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$$. You can continue to access the data in a
dropped table or even use Flashback Query against it. 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. 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
o A user creates a new table or adds data that causes their quota to be exceeded.
o The tablespace needs to extend its file size to accommodate create/insert operations.
There are no issues with dropping the table, behaviour wise. It is the same as in 8i / 9i. The space is not released immediately and is accounted for within the same tablespace / schema after the drop.
When we drop a tablespace or a user there is NO recycling of the objects.
o Recyclebin does not work for SYS owned objects
EXAMPLE
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for Solaris: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
SQL> sho user
USER is "BH"
SQL> SELECT object_name,original_name,operation,type,dropscn,droptime
2 FROM user_recyclebin
3 /
no rows selected
SQL> CREATE TABLE t1(a NUMBER);
Table created.
SQL> DROP TABLE t1;
Table dropped.
SQL> SELECT object_name,original_name,operation,type,dropscn,droptime
2 FROM user_recyclebin
3 /
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPSCN DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ---------- -------------------
BIN$1Unhj5+DSHDgNAgAIKds8A==$0 T1 DROP TABLE 8.1832E+12 2004-03-10:11:03:49
SQL> sho user
USER is "SYS"
SQL> SELECT owner,original_name,operation,type
2 FROM dba_recyclebin
3 /
OWNER ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- ------
BH T1 DROP TABLE
We can also create a new table with the same name at this point.
PURGING
In order to completely remove the table from the DB and to release the space the new PURGE command is used.
From BH user:
SQL> PURGE TABLE t1;
Table purged.
OR
SQL> PURGE TABLE "BIN$1UtrT/b1ScbgNAgAIKds8A==$0";
Table purged.
From SYSDBA user:
SQL> SELECT owner,original_name,operation,type
2 FROM dba_recyclebin
3 /
no rows selected
From BH user:
SQL> SHOW recyclebin
SQL>
There are various ways to PURGE objects:
PURGE TABLE t1;
PURGE INDEX ind1;
PURGE recyclebin; (Purge all objects in Recyclebin)
PURGE dba_recyclebin; (Purge all objects / only SYSDBA can)
PURGE TABLESPACE users; (Purge all objects of the tablespace)
PURGE TABLESPACE users USER bh; (Purge all objects of the tablspace belonging to BH)
For an object, the owner or a user with SYSDBA privilege or a user with DROP ANY... system privilege for the type of object to be purged can PURGE it.
For more information on the PURGE command refer to the 10g SQL Reference
DISABLING RECYCLEBIN
We can DROP and PURGE a table with a single command
From BH user:
SQL> DROP TABLE t1 PURGE;
Table dropped.
SQL> SELECT *
2 FROM recyclebin
3 /
no rows selected
There is no need to PURGE.
On 10gR1, in case we want to disable the behavior of recycling, there is an underscore parameter
"_recyclebin" which defaults to TRUE. We can disable recyclebin by setting it to FALSE.
From SYSDBA user:
SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin TRUE TRUE
From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.
SQL> DROP TABLE t1;
Table dropped.
SQL> SELECT original_name
FROM user_recyclebin;
ORIGINAL_NAME
--------------
T1
From SYSDBA user:
SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;
System altered.
SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin FALSE TRUE
From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.
SQL> DROP TABLE t1;
Table dropped.
SQL> SELECT original_name
FROM user_recyclebin;
no rows selected
There is no need to PURGE.
As with anyother underscore parameter, setting this parameter is not recommended unless
advised by oracle support services.
On 10gR2 and higher; recyclebin is a initialization parameter and bydefault its ON.
We can disable recyclebin by using the following commands:
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.
SQl> Select object_type,object_name from dba_objects where object_name='RECYCLEBIN';
OBJECT_TYPE OBJECT_NAME
-----------------------------------------------
SYNONYM RECYCLEBIN
SQL> desc recyclebin
Name Null? Type
----------------------------------------- -------- ------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER
Related recyclebin objects:
SQL> SELECT SUBSTR(object_name,1,50),object_type,owner
FROM dba_objects
WHERE object_name LIKE '%RECYCLEBIN%';
/
SUBSTR(OBJECT_NAME,1,50) OBJECT_TYPE OWNER
--------------------------- ------------------- ------------ ----------
RECYCLEBIN$ TABLE SYS
RECYCLEBIN$_OBJ INDEX SYS
RECYCLEBIN$_TS INDEX SYS
RECYCLEBIN$_OWNER INDEX SYS
USER_RECYCLEBIN VIEW SYS
USER_RECYCLEBIN SYNONYM PUBLIC
RECYCLEBIN SYNONYM PUBLIC
DBA_RECYCLEBIN VIEW SYS
DBA_RECYCLEBIN SYNONYM PUBLIC
9 rows selected.
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. If table EMP was created in the USERS tablespace, the dropped table EMP 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$$. You can continue to access the data in a
dropped table or even use Flashback Query against it. 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. 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
o A user creates a new table or adds data that causes their quota to be exceeded.
o The tablespace needs to extend its file size to accommodate create/insert operations.
There are no issues with dropping the table, behaviour wise. It is the same as in 8i / 9i. The space is not released immediately and is accounted for within the same tablespace / schema after the drop.
When we drop a tablespace or a user there is NO recycling of the objects.
o Recyclebin does not work for SYS owned objects
EXAMPLE
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for Solaris: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
SQL> sho user
USER is "BH"
SQL> SELECT object_name,original_name,operation,type,dropscn,droptime
2 FROM user_recyclebin
3 /
no rows selected
SQL> CREATE TABLE t1(a NUMBER);
Table created.
SQL> DROP TABLE t1;
Table dropped.
SQL> SELECT object_name,original_name,operation,type,dropscn,droptime
2 FROM user_recyclebin
3 /
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPSCN DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ---------- -------------------
BIN$1Unhj5+DSHDgNAgAIKds8A==$0 T1 DROP TABLE 8.1832E+12 2004-03-10:11:03:49
SQL> sho user
USER is "SYS"
SQL> SELECT owner,original_name,operation,type
2 FROM dba_recyclebin
3 /
OWNER ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- ------
BH T1 DROP TABLE
We can also create a new table with the same name at this point.
PURGING
In order to completely remove the table from the DB and to release the space the new PURGE command is used.
From BH user:
SQL> PURGE TABLE t1;
Table purged.
OR
SQL> PURGE TABLE "BIN$1UtrT/b1ScbgNAgAIKds8A==$0";
Table purged.
From SYSDBA user:
SQL> SELECT owner,original_name,operation,type
2 FROM dba_recyclebin
3 /
no rows selected
From BH user:
SQL> SHOW recyclebin
SQL>
There are various ways to PURGE objects:
PURGE TABLE t1;
PURGE INDEX ind1;
PURGE recyclebin; (Purge all objects in Recyclebin)
PURGE dba_recyclebin; (Purge all objects / only SYSDBA can)
PURGE TABLESPACE users; (Purge all objects of the tablespace)
PURGE TABLESPACE users USER bh; (Purge all objects of the tablspace belonging to BH)
For an object, the owner or a user with SYSDBA privilege or a user with DROP ANY... system privilege for the type of object to be purged can PURGE it.
For more information on the PURGE command refer to the 10g SQL Reference
DISABLING RECYCLEBIN
We can DROP and PURGE a table with a single command
From BH user:
SQL> DROP TABLE t1 PURGE;
Table dropped.
SQL> SELECT *
2 FROM recyclebin
3 /
no rows selected
There is no need to PURGE.
On 10gR1, in case we want to disable the behavior of recycling, there is an underscore parameter
"_recyclebin" which defaults to TRUE. We can disable recyclebin by setting it to FALSE.
From SYSDBA user:
SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin TRUE TRUE
From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.
SQL> DROP TABLE t1;
Table dropped.
SQL> SELECT original_name
FROM user_recyclebin;
ORIGINAL_NAME
--------------
T1
From SYSDBA user:
SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;
System altered.
SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin FALSE TRUE
From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.
SQL> DROP TABLE t1;
Table dropped.
SQL> SELECT original_name
FROM user_recyclebin;
no rows selected
There is no need to PURGE.
As with anyother underscore parameter, setting this parameter is not recommended unless
advised by oracle support services.
On 10gR2 and higher; recyclebin is a initialization parameter and bydefault its ON.
We can disable recyclebin by using the following commands:
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.
