Home > RECYCLE BIN > RECYCLE BIN

RECYCLE BIN

Oracle has introduced the RECYCLE BIN which is a logical entity to hold all the deleted objects and works exactly like the recycle bin provided in Windows operating system for example. All the deleted objects are kept n the recycle bin; these objects can be retrieved from the recycle bin or deleted permanently by using the PURGE command. Either an individual object like a table or an index can be deleted from the recycle bin

Related Data Dictionary Objects

·         recyclebin$
·         dba_recyclebin   
·         recyclebin 
·         user_recyclebin

user_recyclebin and dba_recyclebin are use for recovery using flashback

 
SQL> SELECT name, value
FROM v$parameter
WHERE name LIKE 'recyc%';

NAME             VALUE       
----------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
recyclebin       on       
 

How to Use Recycle Bin in Oracle 10g

Starting and stopping the recyclebin

Syntax: 
ALTER SYSTEM SET recyclebin=<OFF | ON> SCOPE=<BOTH | MEMORY | SPFILE>;

By default recyclebin is set to on

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from  dba_recyclebin;
no rows selected
SQL> drop table scott.emp;
Table dropped.

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from dba_recyclebin;
OBJECT_NAME                              ORIGINAL_NAME        TYPE
-----------------------------------------------------------------
BIN$iSsOuMCxThKB65n/ep2Y5g==$0    PK_EMP        INDEX
BIN$JsuRtykaSpOd8XLplx1/vA==$0    EMP           TABLE

To recover the emp table issue the following command

Syntax:
FLASHBACK TABLE <table_name> TO BEFORE DROP {RENAME TO <new_table_name>};

SQL> flashback table scott.emp to before drop;
Flashback complete.
Now emp table is restored from recyclebin.

You can also rename the table during the time of restore

SQL> flashback table scott.emp to before drop
     RENAME TO scott.emp2; 

To clear the recycle bin issue the following command

Syntax:
PURGE TABLE <recycle_bin_name>;
 
SQL> PURGE TABLE scott.emp;
 
Table purged.

Remove Recycle Bin Objects by Tablespace and User

Syntax:
PURGE TABLESPACE <tablespace_name> 
USER <schema_name>;
 
SQL Code:
PURGE TABLESPACE users USER scott;

Clear full recycilebin

Syntax:
PURGE RECYCLEBIN

Empty Everything in All Recycle Bins

SQL Code:
PURGE dba_recyclebin;
 
 
Advertisements
Categories: RECYCLE BIN Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: