Skip to content

How Oracle Restore Table from Recycle Bin

  • by

Restoring Dropped Table

There're several ways that can recover your table:

  1. Export/Import:
  2. You can use impdp (or imp) utility to import your dropped table from dump files that were exported by expdp (or exp) against your database before.

  3. RMAN:
  4. You can perform a tablespace point-in-time recovery (TSPITR) in another database and move it back to your target database.

  5. Standby Database:
  6. You can retrieve the whole table from any kind of your standby database if it was designed to delay applying the newest logs deliberately.

  7. Recycle Bin:
  8. For 10g or later version, you can flashback dropped tables from recycle bin. This is what we're going to learn in this post.

Restoring Dropped Table from Recycle Bin

I think you may feel lucky if your database version is 10g or later. Because you have recycle bin, a flashback technology that is widely used among DBA to fix user failures.

On the other side, there're several way to purge recycle bin if you decide to abandon those objects.

Parameter RECYCLEBIN

First of all, you have to make sure your RECYCLEBIN is ON across the database before restoring any dropped tables from the recycle bin.

[oracle@test ~]$ sqlplus / as sysdba
...
SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

Dropping an Object

Now, let's drop a table for instance.

SQL> conn hr/hr;
Connected.
SQL> desc dummy;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(100)

SQL> drop table dummy;

Table dropped.

Looking for Dropped Objects

Check the content of HR's recycle bin.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
DUMMY            BIN$cxP0p6/dEnrgVQAAAAAAAQ==$0 TABLE        2018-08-10:19:22:14

The dropped table DUMMY does exist in the recycle bin of user HR. In this way, you can only show your own dropped objects.

Let's make sure the data before restoring.

SQL> select * from "BIN$cxP0p6/dEnrgVQAAAAAAAQ==$0";

COL1
--------------------------------------------------------------------------------
Ed Chen

Flashback Objects

You can restore the dropped table to original name. It's just like nothing changed.

SQL> flashback table dummy to before drop;

Flashback complete.

SQL> drop table dummy;

Table dropped.

Or rename the table if you concern about name collision.

SQL> flashback table dummy to before drop rename to dummy2;

Flashback complete.

So far so good, we may check further.

SQL> desc dummy;
ERROR:
ORA-04043: object dummy does not exist


SQL> desc dummy2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(100)

Purging Objects or Recycle Bin

If you want to discard this object, just do it like this:

SQL> purge table "BIN$cxP0p6/dEnrgVQAAAAAAAQ==$0";

Table purged.

Or discard all of dropped objects that you owned.

SQL> purge recyclebin;

Recyclebin purged.

SQL> show recyclebin;

There's nothing in recycle bin after purging the whole recycle bin of user HR.

In the above, I used a simple example to demonstrate how to use the recycle bin. In fact, there's more deep scenarios for Using Flashback Drop and Managing the Recycle Bin.

Retention of Dropped Objects

Another question that you may ask is: What is the retention window of recycle bin by default? Actually, there's no such retention things at all. Theoretically, you can keep dropped objects as long as you want until you purge them explicitly, otherwise they will be purged by the database once tablespace faces space pressure. Which means, you should count dropped objects in their owner's space quota respectively.

Leave a Reply

Your email address will not be published.