Recycle Bin in Oracle

Oracle database has the recycle bin which is a data dictionary table and contains information about dropped objects.

Until we don’t use the purge option, all dropped tables and its objects such as indexes, constraints and etc are not removed and still occupy space.

Checking if the recycle bin is on or off as follows.

SQL> show parameter recyclebin;

NAME                   TYPE                VALUE

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

recyclebin             string                on

To disable and enable the recycle bin (for both session and system)

ALTER SESSION SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = OFF;

ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = ON;

Restore Dropped Tables from Recycle Bin

Stimulating a drop and recover table from recycle bin ,

SQL> show parameter recyclebin

NAME TYPE VALUE

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

recyclebin string on

Viewing table:

SQL> select count(*) from emp;

COUNT(*)

----------

50

dropping table:

SQL> drop table emp;


Table dropped.

Check if it exists.

SQL> select * from emp;

select * from emp

*

ERROR at line 1:

ORA-00942: table or view does not exist

Now lets go to flashback table from recycle bin as follows.

SQL> flashback table emp to before drop;


Flashback complete.

Check if table exists or not.

SQL> select count(*) from emp;

COUNT(*)

----------

50

we can list the objects and droptime from recycle bin as follows.

SQL> select object_name, droptime from dba_recyclebin;


OBJECT_NAME DROPTIME

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

BIN$4YkbXtBoAdngQwEAAH8n+g==$0 2021-03-28:12:49:57

BIN$4YkbXtBpAdngQwEAAH8n+g==$0 2021-03-28:12:49:57

BIN$4YkbXtBqAdngQwEAAH8n+g==$0 2021-03-28:12:49:57

BIN$4YkbXtBrAdngQwEAAH8n+g==$0 2021-03-28:12:49:57

Purge Recyclebin:

we can purge recycle bin as follows. purge recyclebin will remove all objects from the user’s recycle bin and release all space associated

with objects in the recycle bin

SQL> purge recyclebin;

Recyclebin purged.

Purge DBA_RECYCLEBIN:

Purge dba_recyclebin needs sysdba privilige and it removes all objects from the system-wide recycle bin, and is equivalent to purging the recycle bin of every user

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Tablespace Purge:

purge tablespace option purges all the objects residing in the specified tablespace from the recycle bin.

SQL> purge tablespace Users;

Tablespace purged.

You can purge any table as follows.

SQL> PURGE TABLE emp;

Table purged.

You can purge any dropped table as follows.

SQL> purge table "BIN$cxP2b6/dEnrgVQBAAAAADQ==$1";


Table purged.

Sagayaraj Rajamanickam