Table Recovery

Table recovery in Oracle 10g

Many a times it happens that a developer or sometimes you as a DBA, accidently drops a table and want to correct things by restoring it back.

Oracle9i Database introduced the concept of a Flashback Query option to retrieve data from a point in time in the past, but it can’t flash back DDL operations such as dropping a table. The only recourse is to use table space point-in-time recovery in a different database and then recreate the table in the current database using export/import or some other method. This procedure demands significant DBA effort as well as precious time, not to mention the use of a different database for cloning

However Oracle 10g came with excellent feature table recovery is as simple as that running few very simple commands. 

We discuss this feature in this blog:

SQL> Select banner from v$version ;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

T1                                       TABLE

TEST_NULL                      TABLE

SQL> Drop Table T1;

Table dropped.

SQL> select * from tab;

BIN$/sNVxevp5A/gQKjAEQAYwA==$0 TABLE

Notice! We used “Drop Table T1” statement and expected that table is dropped and spaced is freed. But this is not so. Instead a new table has come with some bizarre name. This is the name that Oracle gave to our table T1.

What happened is Oracle tossed a new terminology called RECYCLEBIN just like the recycle bin that we have in our Windows machines. Instead of dropping table and freeing space, Oracle kept table in Recyclebin for just in case DBA wants to flashback it.

Let’s see what else RECYCLEBIN has stored there.

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                                       OBJECT TYPE                  DROP TIME

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

CURRENCY_TEST    BIN$/mGWpLuT4DLgQKjAEQAWBg==$0 TABLE                   2014-07-17:16:46:32

CURRENCY_TEST    BIN$/mGWpLuS4DLgQKjAEQAWBg==$0 TABLE                   2014-07-17:16:33:19

T1               BIN$/sNVxevp5A/gQKjAEQAYwA==$0                    TABLE                    2014-07-22:12:24:08

See! Except others I have my T1 table stored in there. Now lets see the magic.

SQL> Flashback table t1 to before drop;

Flashback complete.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

T1                                     TABLE

See. We got over table back at place. J

Now one important question arises how I can drop my table completely if at all we want to drop it. In this case use PURGE option of drop table.

SQL> Drop Table T1 Purge;

Table dropped.

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                                  OBJECT TYPE  DROP TIME

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

CURRENCY_TEST    BIN$/mGWpLuT4DLgQKjAEQAWBg==$0 TABLE        2014-07-17:16:46:32

CURRENCY_TEST    BIN$/mGWpLuS4DLgQKjAEQAWBg==$0 TABLE        2014-07-17:16:33:19

This time, We did not get table listed in RECYCLEBIN

This particular operation  related to PURGE can be done in another way. Once we drop the Table without PURGE clause we can use PURGE Clause as follows to remove table from RECYCLEBIN.

SQL> Purge table t1;

Table purged.

Sometimes it happens that you dropped lots of tables from one particular tablespace. So instead of doing PURGE one by one, you can use following command simply to remove all entries in one go.

In this example, I had all of my tables in USERTBS tablespace. So I used following command to remove all tables from RECYCLEBIN.

SQL> Purge Tablespace USERTBS;

Tablespace purged.

Similarly, If you had dropped tables related to one particular user than you can use variant of this particular command.

SQL> Purge Tablespace UserTBS user test;

Tablespace purged.

                               

                                                                OR

Connect to TEST user and run following command.

SQL> Purge Recyclebin;

Recyclebin purged.

You as DBA can use following command to remove list of tables related to every tablespace.

SQL> Purge DBA_RECYCLEBIN;

This is not all. Using Flashback Table <> to Drop we can flashback table to particular time or a scn.

FLASHBACK TABLE T1 TO SCN 285954520;

Renaming is also possible.

FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST2;

FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;

This feature has definitely saved us from lots of trouble in situation like this.

Any comment, suggestion would be appreciated.