如果User_recyclebin中有多个同名的表,在做purge table 与 flahback table 的时候
应该优先清除那个,优先恢复那个呢。
purge 清除第一次被drop掉的表, flashback恢复最后一次被drop掉的表。
一、首先构造三个相同表名,但结构不同的表
SQL> create table JOBS
2 (
3 MIN_SALARY NUMBER(6),
4 MAX_SALARY NUMBER(6)
5 );
Table created
SQL> DESC JOBS;
Name Type Nullable Default Comments
---------- --------- -------- ------- --------
MIN_SALARY NUMBER(6) Y
MAX_SALARY NUMBER(6) Y
SQL> DROP TABLE JOBS;
Table dropped
SQL>
SQL> create table JOBS
2 (
3 JOB_ID VARCHAR2(10),
4 JOB_TITLE VARCHAR2(35) not null
5 );
Table created
SQL> DESC JOBS;
Name Type Nullable Default Comments
--------- ------------ -------- ------- --------
JOB_ID VARCHAR2(10) Y
JOB_TITLE VARCHAR2(35)
SQL> DROP TABLE JOBS;
Table dropped
SQL>
SQL> create table JOBS
2 (
3 JOB_ID VARCHAR2(10),
4 JOB_TITLE VARCHAR2(35) not null,
5 MIN_SALARY NUMBER(6),
6 MAX_SALARY NUMBER(6)
7 );
Table created
SQL> DESC JOBS;
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
JOB_ID VARCHAR2(10) Y
JOB_TITLE VARCHAR2(35)
MIN_SALARY NUMBER(6) Y
MAX_SALARY NUMBER(6) Y
SQL> DROP TABLE JOBS;
Table dropped
二、查询User_recyclebin 中有三个同名的表JOBS
SQL> SELECT original_name,object_name, droptime FROM user_recyclebin WHERE original_name = 'JOBS';
ORIGINAL_NAME OBJECT_NAME DROPTIME
--------------- ------------------------------ -------------------
JOBS BIN$l+JbdMeTkFzgQwoBAQKQXA==$0 2010-12-19:09:18:33
JOBS BIN$l+JbdMeVkFzgQwoBAQKQXA==$0 2010-12-19:09:18:48
JOBS BIN$l+JbdMeXkFzgQwoBAQKQXA==$0 2010-12-19:09:18:58
a)进行purge操作,应该把2010-12-19:09:18:33记录清除
SQL> PURGE TABLE JOBS;
Done
SQL> SELECT original_name,object_name, droptime FROM user_recyclebin WHERE original_name = 'JOBS';
ORIGINAL_NAME OBJECT_NAME DROPTIME
--------------- ------------------------------ -------------------
JOBS BIN$l+JbdMeVkFzgQwoBAQKQXA==$0 2010-12-19:09:18:48
JOBS BIN$l+JbdMeXkFzgQwoBAQKQXA==$0 2010-12-19:09:18:58
b)flashback应该恢复2010-12-19:09:18:58条记录
SQL> FLASHBACK TABLE JOBS TO BEFORE DROP;
Done
SQL> SELECT original_name,object_name, droptime FROM user_recyclebin WHERE original_name = 'JOBS';
ORIGINAL_NAME OBJECT_NAME DROPTIME
--------------- ------------------------------ -------------------
JOBS BIN$l+JbdMeVkFzgQwoBAQKQXA==$0 2010-12-19:09:18:48
SQL>