RECYCLE BIN concept has been
in introduced in Oracle 10g onwards. This is similar to WINDOWS RECYCLEBIN and
objects are stored in FLASHBACK area.
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 is 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 objects
a) Check current value for recyclebin and
change to “ON”
SQL> show parameter recycle
NAME TYPE VALUE
———————————— ———– ——————————
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string OFF
SQL> alter system set recyclebin=on scope=spfile;
System altered.
b) Restart the DB and check the
value.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size 2228824 bytes
Variable Size 1056968104 bytes
Database Buffers 553648128 bytes
Redo Buffers 7270400 bytes
Database mounted.
Database opened.
SQL> show parameter recycle
NAME TYPE VALUE
———————————— ———– ——————————
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string ON
SQL>
2) How to disable recycle bin
in oracle.
a) Check current value for
recyclebin and change to “ON”
SQL> show parameter recycle
NAME TYPE VALUE
———————————— ———– ——————————
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string ON
SQL> alter system set recyclebin=OFF
scope=spfile;
System altered.
b) Restart the DB and check the
value.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size 2228824 bytes
Variable Size 1056968104 bytes
Database Buffers 553648128 bytes
Redo Buffers 7270400 bytes
Database mounted.
Database opened.
SQL> show parameter recycle
NAME TYPE VALUE
———————————— ———– ——————————
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string OFF
SQL>
3) How to purge recyclebin?
Purging can be done at user level or DB level. If we need to purge at
User level only tables related to user only will be deleted but if we delete at
DB level, all tables will be purged.
a) Purging a specific table
- Connect to user.
- Check the current entries
using user_recyclebin;
- Purge the epcific table
using “purge table <table_name>;”
- Check the current entries
using user_recyclebin;
E.g.:-
SQL> create table mytesttbl2(srno number,name
varchar2(20));
Table created.
SQL> drop table mytesttbl2;
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$30N07CjBfIvgQz7c5gqzPQ==$0 mytesttbl2 DROP TABLE 8.1832E+12 2004-03-10:11:03:49
SQL> PURGE TABLE mytesttbl2;
Table purged.
b) Purging at user level.
- Connect to user.
- Check the current entries using user_recyclebin;
- Purge the recyclebin using “purge recyclebin;”
- Check the current entries using user_recyclebin;
E.g.:-
SQL> select count(1) from user_recyclebin;
COUNT(1)
———-
0
SQL> create table mytesttbl2(srno number,name
varchar2(20));
Table created.
SQL> drop table mytesttbl2;
Table dropped.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
BIN$30N07CjBfIvgQz7c5gqzPQ==$0 TABLE
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
MYTESTTBL TABLE
REGIONS TABLE
10 rows selected.
SQL> select count(1) from user_recyclebin;
COUNT(1)
———-
1
SQL> purge recyclebin;
Recyclebin purged.
SQL> select count(1) from user_recyclebin;
COUNT(1)
———-
0
c) Purging at DB level.
- Connect to DB as sysdba user.
- Check the current entries using dba_recyclebin;
- Purge the recyclebin using “purge DBA_recyclebin;”
- Check the current entries using DBA_recyclebin;
E.g.: – $ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun
16 05:53:13 2013
Copyright (c) 1982, 2011, Oracle. All rights
reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release
11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> select count(1) from dba_recyclebin;
COUNT(1)
———-
0
SQL> /
COUNT(1)
———-
1
SQL> /
COUNT(1)
———-
0
SQL> select count(1) from dba_recyclebin;
COUNT(1)
———-
1
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> select count(1) from dba_recyclebin;
COUNT(1)
———-
0
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 <user name>; (Purge all objects of the
tablspace belonging to specific user)
I hope this article helped you. Your suggestions/feedback are most welcome.
Keep learning... Have a great day!!!
