29 November, 2012

Recycle Bin in Database


Oracle introduced in database 10g new feature called "Recycle Bin" to store the dropped database objects.
If any table is dropped then any associated object to this table such as indexes, constraints and any other dependent object are renamed with a prefix of bin$$.

Use of Recycle Bin
If user drop an important object accidentally, and he want to get it again.
With Recycle Bin feature user can easily restore the dropped objects.

Enable and Disable Recycle Bin
You can use the below query to distinguish which Recycle Bin is enabled or no

 SELECT Value FROM V$parameter WHERE Name = 'recyclebin';  

It will return on or off
on means that Recycle Bin is enabled and off is disabled.

You can enable and disable Recycle Bin per session and system, there fore you can use the below scripts to enable and disable Recycle Bin per session or system.

 ALTER SYSTEM SET recyclebin = ON;  
   
 ALTER SESSION SET recyclebin = ON;  
   
 ALTER SYSTEM SET recyclebin = OFF;  
   
 ALTER SESSION SET recyclebin = OFF;  



Get Contents of Recycle Bin
To get the dropped object in Recycle Bin, you can use any one of the below query statements.

 SELECT * FROM RECYCLEBIN;  
   
 SELECT * FROM USER_RECYCLEBIN;  
   
 SELECT * FROM DBA_RECYCLEBIN;  

Restore Dropped Objects
You can use the below syntax to restore dropped objects

 FLASHBACK TABLE <<Dropped_Table_Name>> TO BEFORE DROP RENAME TO <<New_Table_Name>>;  

Note that RENAME TO portion in restore statement is optional and you should use it if you want to restore dropped object with new name.

Clearing the Recycle Bin
You can clear specific entries in Recycle Bin or complete Recycle Bin

a- Clear Specific Table
PURGE TABLE <<Table_NAME>>;

b- Clear specific index
PURGE INDEX <<Index_NAME>>;

c- Clear every objects associated with specific table space
PURGE TABLESPACE<<Table_NAME>>;

d- Clear objects of a specific user in table space
PURGE TABLESPACE<<Table_NAME>> USER <<User_Name>>;

e- Clear complete Recycle Bin
PURGE TABLE <<Table_NAME>>;

e- Clear Complete Recycle Bin
PURGE RECYCLEBIN;

f- You can clear the table from RECYCLE Bin while dropping it
DROP TABLE <<Table_Name>> PURGE;

Demo
Now I will take a demo and for clarifying  Recycle Bin feature

1-Enable Recycle Bin feature

ALTER SYSTEM SET recyclebin = ON;  

2- Create DEMO_RECYCLEBIN database table

 CREATE TABLE DEMO_RECYCLEBIN (COL1 NUMBER);  

3- Insert one record in DEMO_RECYCLEBIN table

 INSERT INTO DEMO_RECYCLEBIN (COL1)  
    VALUES (1);  
   
 COMMIT;  

4- Drop  DEMO_RECYCLEBIN  table

 DROP TABLE DEMO_RECYCLEBIN;  

5- Query the Recycle Bin contents

SELECT * FROM USER_RECYCLEBIN;  

The data will be like below

6- Restore DEMO_RECYCLEBIN table from Recycle Bin

 FLASHBACK TABLE DEMO_RECYCLEBIN TO BEFORE DROP;  

7- Quert DEMO_REYCLEBIN after restoring

 SELECT * FROM DEMO_RECYCLEBIN;  

It will return the data existed before dropping

8- Drop table again and clear the Recycle Bin

 DROP TABLE DEMO_RECYCLEBIN PURGE;  


Thanks

3 comments:

  1. Your blog name, "Dive in ..." combined with your post topic, recycle bins, makes for a dive into a recycle bin. Are you taking us into the trash? ;-)

    Enjoyed reading all this information in one place. Thanks for putting it together.

    ReplyDelete
  2. Yes, the RECYCLEBIN does preserve dropped objects but it apparently only preserves the table name on flashback -- constraints and indexes are restored with the RECYCLEBIN name and not with the name given at time of creation:

    SQL> create table recycle_demo (democol varchar2(40) , democol2 number);

    Table created.

    SQL>
    SQL> alter table recycle_demo add constraint recycle_pk primary key(democol);

    Table altered.

    SQL>
    SQL> create index recycle_idx on recycle_demo(democol2);

    Index created.

    SQL>
    SQL> begin
    2 for i in 1..2000 loop
    3 insert into recycle_demo
    4 values(to_char(to_date(i, 'J'), 'Jsp'), i);
    5 end loop;
    6
    7 commit;
    8
    9 end;
    10 /

    PL/SQL procedure successfully completed.

    SQL>
    SQL> select count(*) from recycle_demo;

    COUNT(*)
    ----------
    2000

    SQL>
    SQL> select constraint_name, table_name from user_constraints;

    CONSTRAINT_NAME TABLE_NAME
    ------------------------------ ------------------------------
    RECYCLE_PK RECYCLE_DEMO

    SQL>
    SQL> select index_name, table_name From user_indexes;

    INDEX_NAME TABLE_NAME
    ------------------------------ ------------------------------
    RECYCLE_PK RECYCLE_DEMO
    RECYCLE_IDX RECYCLE_DEMO

    SQL>
    SQL> drop table recycle_demo;

    Table dropped.

    SQL>
    SQL> desc recycle_demo
    ERROR:
    ORA-04043: object recycle_demo does not exist


    SQL>
    SQL> select object_name, original_name From user_recyclebin;

    OBJECT_NAME ORIGINAL_NAME
    ------------------------------ --------------------------------
    BIN$MTA5QisvSJulzTfYz7u0DA==$0 RECYCLE_IDX
    BIN$4VeeHI2ZQZiraidpzt2UVg==$0 RECYCLE_PK
    BIN$7iCFsmiPQAikil3mE/ArRA==$0 RECYCLE_DEMO

    SQL>
    SQL> flashback table recycle_demo to before drop;

    Flashback complete.

    SQL>
    SQL> desc recycle_demo
    Name Null? Type
    ------------------------------------------------------------------------ -------- -------------------------------------------------
    DEMOCOL NOT NULL VARCHAR2(40)
    DEMOCOL2 NUMBER

    SQL>
    SQL> select object_name, original_name from user_recyclebin;

    no rows selected

    SQL>
    SQL> select constraint_name, table_name from user_constraints;

    CONSTRAINT_NAME TABLE_NAME
    ------------------------------ ------------------------------
    BIN$zaItSuJuT6G32iB4d/+Mnw==$0 RECYCLE_DEMO

    SQL>
    SQL> select index_name, table_name From user_indexes;

    INDEX_NAME TABLE_NAME
    ------------------------------ ------------------------------
    BIN$4VeeHI2ZQZiraidpzt2UVg==$0 RECYCLE_DEMO
    BIN$MTA5QisvSJulzTfYz7u0DA==$0 RECYCLE_DEMO

    SQL>
    SQL> select count(*) from recycle_demo;

    COUNT(*)
    ----------
    2000

    SQL>
    SQL> drop table recycle_demo;

    Table dropped.

    SQL>
    SQL> select object_name, original_name from user_recyclebin;

    OBJECT_NAME ORIGINAL_NAME
    ------------------------------ --------------------------------
    BIN$PG4JKSZRQZqqpf7JuquoPA==$1 BIN$MTA5QisvSJulzTfYz7u0DA==$0
    BIN$wjZQslRjQDeoMcRXA3M9Ug==$1 BIN$4VeeHI2ZQZiraidpzt2UVg==$0
    BIN$fmn6OiEwQt+nD7ZMpAk3ig==$0 RECYCLE_DEMO

    SQL>
    SQL> purge table recycle_demo;

    Table purged.

    SQL>
    SQL> select object_name, original_name from user_recyclebin;

    no rows selected

    SQL>

    ReplyDelete
  3. help full thank you all.

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...