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;

 

Reference: Recycle Bin in Database from our JCG partner Mahmoud A. ElSayed at the Dive in Oracle blog.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

Leave a Reply


six + = 10



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close