RESTORE DROPPED TABLES FROM RECYCLE BIN

DESCRIPTION:

In this blog,we are going to see how to restore dropped table from recyclebin.

Oracle database has the recycle bin which is a data dictionary table and contains information about dropped objects. Until you don’t use purge option, all dropped tables and its objects such as indexes, constraints and etc are not removed and still occupy space.

SQL> show parameter recyclebin;

NAME                   TYPE                VALUE

------------------------------------ -----------

recyclebin             string                on

 

To disable the recycle bin, you can disable the recycle bin both for session and system level as follows.

ALTER SESSION SET recyclebin = OFF;

SQL> show parameter recyclebin;

NAME                   TYPE                VALUE

------------------------------------ -----------

recyclebin             string                off

we can enable the recycle bin both for session and system level as follows.

ALTER SESSION SET recyclebin = ON;

To recover a dropped table from recycle bin as follows.

Following table has 21 rows.

SQL> select count(*) from Tab1;

COUNT(*)

----------

21

SQL> drop table Tab1;

Table dropped.

Check if it exists.

SQL> select * from Tab1;

select * from Tab1

*

ERROR at line 1:

ORA-00942: table or view does not exist

Now lets go to flashback table from recycle bin as follows.

SQL> flashback table Tab1 to before drop;

Flashback complete.

Check if table exists or not.

SQL> select count(*) from Tab1;

COUNT(*)

----------

21