This article describes the behavior of PURGE and FLASHBACK TO BEFORE DROP when we have multiple table in the recyclebin with same name
This article can be used by Oracle Sipport Analysts and DBAs
We can have a situation when we have more than one table in the recyclebin having same name. If we are going to PURGE the table, the oldest one will be purged from the recyclebin. If we are going to restore(FLASHBACK BEFORE DROP) the table, the latest one will be restored.
Example: We will create five tables and drop them:
CREATE TABLE t1(a NUMBER) / DROP TABLE t1 / CREATE TABLE t1(a varchar2(10)) / DROP TABLE t1 / CREATE TABLE t1(a date) / DROP TABLE t1 / CREATE TABLE t1(a varchar2(5)) / DROP TABLE t1 / CREATE TABLE t1(a number) / DROP TABLE t1 /
All the five tables are there in the recyclebin.
SQL> SELECT object_name,original_name,droptime,dropscn 2 FROM recyclebin 3 / OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN ------------------------------ -------------------------------- ------------------- ---------- RB$$48579$TABLE$0 T1 2004-03-25:22:22:35 1302338 RB$$48580$TABLE$0 T1 2004-03-25:22:23:32 1302470 RB$$48581$TABLE$0 T1 2004-03-25:22:24:13 1302665 RB$$48582$TABLE$0 T1 2004-03-25:22:24:54 1302826 RB$$48583$TABLE$0 T1 2004-03-25:22:25:10 1302891
Now if we are going to PURGE table T1 then table with dropscn=1302338 will be purged.
SQL> PURGE TABLE t1; Table purged. SQL> SELECT object_name,original_name,droptime,dropscn 2 FROM recyclebin 3 / OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN ------------------------------ -------------------------------- ------------------- ---------- RB$$48580$TABLE$0 T1 2004-03-25:22:23:32 1302470 RB$$48581$TABLE$0 T1 2004-03-25:22:24:13 1302665 RB$$48582$TABLE$0 T1 2004-03-25:22:24:54 1302826 RB$$48583$TABLE$0 T1 2004-03-25:22:25:10 1302891
And if we restore the table, T1 with dropscn=1302891 will be restored.
SQL> SELECT object_name,original_name,droptime,dropscn 2 FROM recyclebin 3 / OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN ------------------------------ -------------------------------- ------------------- ---------- RB$$48580$TABLE$0 T1 2004-03-25:22:23:32 1302470 RB$$48581$TABLE$0 T1 2004-03-25:22:24:13 1302665 RB$$48582$TABLE$0 T1 2004-03-25:22:24:54 1302826
To overcome this behavior we can use the dropped object name instead of the original name. Since the dropped object name is a unique name (BIN$$ in 10.1.0.2) we can PURGE , restore the desired table irrespective of its dropped time.
Examples:
SQL> FLASHBACK TABLE RB$$48581$TABLE$0 TO BEFORE DROP RENAME TO t1_scn_1302665; Flashback complete. SQL> SELECT object_name,original_name,droptime,dropscn 2 FROM recyclebin 3 / OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN ------------------------------ -------------------------------- ------------------- ---------- RB$$48580$TABLE$0 T1 2004-03-25:22:23:32 1302470 RB$$48582$TABLE$0 T1 2004-03-25:22:24:54 1302826
The table whose drop time is between the other two in the recyclebin was restored.
Similarly we can have:
SQL> PURGE TABLE RB$$48582$TABLE$0; Table purged. SQL> SELECT object_name,original_name,droptime,dropscn 2 FROM recyclebin 3 / OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN ------------------------------ -------------------------------- ------------------- ---------- RB$$48580$TABLE$0 T1 2004-03-25:22:23:32 1302470
Here the latest table in the recyclebin (NOT the oldest) is purged.