Restoring Dropped Table
There're several ways that can recover your table:
- Export/Import:
- RMAN:
- Standby Database:
- Recycle Bin:
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.
You can perform a tablespace point-in-time recovery (TSPITR) in another database and move it back to your target database.
From 12c, we can perform table point-in-time recovery to restore dropped tables from RMAN backups.
You can retrieve the whole table from any kind of your standby database if it was designed to delay applying the newest logs deliberately.
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
--------------------------------------------------------------------------------
Alex
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.