RECYCLE BIN usage and maintenance

RECYCLE BIN usage and maintenance

 

RECYCLE BIN concept has been in introduced in Oracle 10g onwards.  This is similar to WINDOWS RECYCLEBIN and objects are stored in FLASHBACK area.

1)      How to enable recycle bin.

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 oracle.

Same as above except

Alter system set recyclebin=off scope=spfile;

 

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 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

SQL>

b)      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

SQL>