User_recyclebin中同名表的清除与恢复

如果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>