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

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:
  1. 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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google