Archive

Posts Tagged ‘recyclebin’

Oracle Recyclebin

Oracle recyclebin works pretty much works same like windows recyclebin. Concept was introduced by Oracle in 10g. It’s a pretty useful feature .. let’s say in a situation where you have dropped a table and you want to restore that table without performing db recovery.

You can check if Recyclebin is enabled or not by checking following parameter

SQL> show parameter recyclebin;

Let’s see how it works by creating a dummy user and granting him role to connect

SQL> create user john identified by john;
SQL> grant connect, resource to john;

Below is the list of tables that you can use to query information about recyclebin

Image

If you are connected via john and you dropped something, then you can query user_recyclebin.

Check if something is there in recyclebin or not

select * from user_recyclebin;

Let’s create a table,, insert few records into it.

SQL> create table john.sales ( versioninfo varchar(30), insertdate date);

SQL> insert into john.sales values (‘v1’, sysdate);

SQL> commit;

Now let’s drop the table ..

SQL> drop table john.sales ;

Above statement will drop the table, behind the scenes, it’s just renaming the table sales and putting it in recyclebin. Above statement doesn’t release the space occupied by Table.

Let’s query our recyclebin and see what is sitting there now.

Image

As we saw in above screenshot, object just got renamed to BIN$something.

If you want to permanent delete this table from recyclebin then just issue following SQL Statement

SQL > purge recyclebin;

However, If we want to restore it back from recyclebin, all we have to do it issue following sql statement.

SQL> flashback table john.sales to before drop;

What if, if you would prefer not to send table into recyclebin at the 1st place & delete it permanently. You can issue following SQL statement

SQL> drop table john.sales purge ;

What if, … if i create table sales, then drop it , again create table sales, and then again drop it and repeat this for let’s say 10 times . It will obviously show 10 records in recyclebin starting with BIN$, if you issue SQL command

SQL> flashback table john.sales to before drop;

It would restore the table based on LIFO ( Last In first Out ), however if you want to restore specific version, you can issue following command

SQL> flashback table john.”BIN$2te9VmGlINrgQKjArjhl3A==$0” to before drop;

 

Replace BIN with the version that you would want to restore.

 

 

Advertisements
Categories: Uncategorized Tags: , ,