how to create recycle

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

1. Connect to user.
2. Check the current entries using user_recyclebin;
3. Purge the epcific table using “purge table <table_name>;”
4. 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.

1. Connect to user.
2. Check the current entries using user_recyclebin;
3. Purge the recyclebin using “purge recyclebin;”
4. 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.

1.       Connect to DB as sysdba user.

2.      Check the current entries using dba_recyclebin;

3.      Purge the recyclebin using “purge DBA_recyclebin;”

4.      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!!!