10g Recyclebin Feature…

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to10.2.0.1 - Release: 10.1 to 10.2

Information in this document applies to any platform.

Purpose

This bulletin illustrates the new recyclebin functionalityprovided with the 10g database

Scope and Application

Can be used by Oracle Support Analyst and DBA

10g Recyclebin Features And How To Disable it( _recyclebin)

ABOUT 10g RECYCLEBIN

In order to have FLASHBACK DROP functionality a recyclebin isprovided to every oracle user.

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

The recyclebin is a public synonym and it is based on the viewuser_recyclebin which in turn is based on sys.recyclebin$table.

Related recyclebin objects:

SQL> SELECTSUBSTR(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.

Note:  On 10.2/11g, the droppedtable 

  does not show up in the *_TABLESviews 

  does not show up in*_OBJECTS 

  does show up int the *_SEGMENT views

THE RECYCLE BIN

The Recycle Bin is a virtual container where all droppedobjects reside. Underneath the covers, the objects are occupyingthe same space as when they were created. If table EMP was createdin the USERS tablespace, the dropped table EMP remains in the USERStablespace. Dropped tables and any associated objects such asindexes, constraints, nested tables, and other dependant objectsare not moved, they are simply renamed with a prefix of BIN$$. Youcan continue to access the data in a 

dropped table or even use Flashback Query against it. Eachuser has the same rights and privileges on Recycle Bin objectsbefore it was dropped. You can view your dropped tables by queryingthe new RECYCLEBIN view. Objects in the Recycle Bin will remain inthe database until the owner of the dropped objects decides topermanently remove them using the new PURGE command. The RecycleBin objects are counted against a user's quota. But Flashback Dropis a non-intrusive feature. Objects in the Recycle Bin will beautomatically purged by the space reclamation process if

o A user creates a new table or adds data that causes theirquota to be exceeded. 

o The tablespace needs to extend its file size to accommodatecreate/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 immediatelyand is accounted for within the same tablespace / schema after thedrop.

When we drop a tablespace or a user there is NO recycling ofthe 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> SELECTobject_name,original_name,operation,type,dropscn,droptime

  2  FROMuser_recyclebin

  3  /

no rows selected

SQL> CREATE TABLE t1(a NUMBER);

Table created.

SQL> DROP TABLE t1;

Table dropped.

SQL> SELECTobject_name,original_name,operation,type,dropscn,droptime

  2  FROMuser_recyclebin

  3  /

OBJECT_NAME                 ORIGINAL_NAME                OPERATION TYPE                     DROPSCN DROPTIME

-------------------------------------------------------------- ---------------------------------- ---------- -------------------

BIN$1Unhj5+DSHDgNAgAIKds8A==$0 T1                          DROP     TABLE                  8.1832E+122004-03-10:11:03:49

SQL> sho user

USER is "SYS"

SQL> SELECT owner,original_name,operation,type

  2  FROMdba_recyclebin

  3  /

OWNER                      ORIGINAL_NAME                 OPERATIONTYPE

-------------------------------------------------------------- --------- ------

BH                         T1                          DROP     TABLE

We can also create a new table with the same name at thispoint.

 

PURGING

In order to completely remove the table from the DB and torelease 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  FROMdba_recyclebin

  3  /

no rows selected

From BH user:

SQL> SHOW recyclebin

SQL>

 There are various ways to PURGEobjects:

PURGE TABLE t1;

PURGE INDEX ind1;

PURGE recyclebin; (Purge all objects in Recyclebin)

PURGE dba_recyclebin; (Purge all objects / only SYSDBAcan)

PURGE TABLESPACE users; (Purge all objects of thetablespace)

PURGE TABLESPACE users USER bh; (Purge all objects of thetablspace belonging to BH)

For an object, the owner or a user with SYSDBA privilege or auser with DROP ANY... system privilege for the type of object to bepurged can PURGE it.

For more information on the PURGE command refer to the 10g SQLReference

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 ofrecycling, there is an underscore parameter 

"_recyclebin" which defaults to TRUE. We can disablerecyclebin 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 parameteris not recommended unless 

advised by oracle support services.

On 10gR2 and higher; recyclebin is a initialization parameterand bydefault its ON.

We can disable recyclebin by using the followingcommands:

SQL> ALTER SESSION SET recyclebin = OFF;

SQL> ALTER SYSTEM SET recyclebin = OFF;

The dropped objects, when recyclebin was ON will remain in therecyclebin even if we set the recyclebin parameter to OFF.