목차

Purge, Flashback before Drop while multiple Tables in the Recyclebin with same Name

Purpose

This article describes the behavior of PURGE and FLASHBACK TO BEFORE DROP when we have multiple table in the recyclebin with same name

Scope & Application

This article can be used by Oracle Sipport Analysts and DBAs

Purge, Flashback before Drop while multiple Tables in the Recyclebin with same Name

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

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.