Retrieve / Recover / Restore Dropped Table in Oracle 10g 11g Using Flashback Table Feature

advertisements

_____________________________________________________________________________________________________________________

Suppose by mistake if you drop a table in Oracle 10g Oracle 11g you can retrieve the table using Flashback table feature. Flashback feature works with recycle bin to recover the table. For this the recycle bin must be enabled for the database.

Using this you can check the recycle bin is enabled or not.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE

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

recyclebin                           string      on

Suppose you have a table called TEST and you deleted the table multiple times for some testing purpose. All those will be stored in the recycle bin.

You can see the content of the recycle bin using the following command.

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

TEST             BIN$u9TiZohBenbgQAwKjWdk0g==$0 TABLE        2012-03-22:08:16:57

TEST             BIN$ubS/5JznCSXgQAwKjWcrdA==$0 TABLE        2012-02-24:06:06:26

TEST             BIN$ubS/5JzmCSXgQAwKjWcrdA==$0 TABLE        2012-02-24:06:06:16

TEST             BIN$ubSy6ndac4ngQAwKjWcbhA==$0 TABLE        2012-02-24:06:02:38

Now you can retrieve all the dropped tables into different names. Only one restore can be possible to original name(TEST).

Syntax to Restore the Table

1. Flashback table <table_name> to before Drop;

To rename into Different name:

2. Flashback table <table_name> to before Drop rename to <new table>;

SQL> flashback table test to before drop;

Flashback complete.

SQL> flashback table test to before drop rename to test1;

Flashback complete.

SQL> flashback table test to before drop rename to test2;

Flashback complete.

SQL> flashback table test to before drop rename to test3;

Flashback complete.

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

If the recycle in doesn’t have the table you get the following error.

SQL> flashback table test to  before drop rename to test4;

flashback table test to  before drop rename to test4

*

ERROR at line 1:

ORA-38305: object not in RECYCLE BIN

_____________________________________________________________________________________________________________________

Website Stats