adminschool.net wiki

이 문서는 읽기 전용입니다. 원본을 볼 수는 있지만 바꿀 수는 없습니다. 문제가 있다고 생각하면 관리자에게 문의하세요.
====== Purge, Flashback before Drop while multiple Tables in the Recyclebin with same Name ======
  * 문서 ID:  공지:266477.1
  * 유형:  BULLETIN

===== 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:

<code>
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
/
</code>
All the five tables are there in the recyclebin.

<code>
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
</code>

Now if we are going to PURGE table T1 then table with dropscn=1302338 will be purged.

<code>
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
</code>

And if we restore the table, T1 with dropscn=1302891 will be restored.

<code>
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
</code>

===== 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: 

<code>
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
</code>

The table whose drop time is between the other two in the recyclebin was restored.

Similarly we can have:

<code>
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
</code>

Here the latest table in the recyclebin (NOT the oldest) is purged.
adminschool.net wiki

사용자 도구

사이트 도구

문서 도구
