Oracle 11g 閃回刪除

scott@TESTDB11>select * from cat;

TABLE_NAME                     TABLE_TYPE

------------------------------ -----------

BONUS                          TABLE

DEPT                           TABLE

EMP1                           TABLE

EMP1_LOG                       TABLE

ERRMSG                         TABLE

SALGRADE                       TABLE

STUDENT1                       TABLE

STUDENT2                       TABLE

SYS_TEMP_FBT                   TABLE

TAB_NEWTS                      TABLE

VIEW_STUDENT                   VIEW

11 rows selected.

--刪除表

scott@TESTDB11>drop table student1;

Table dropped.

scott@TESTDB11>drop table student2;

Table dropped.

--出現在回收站中

scott@TESTDB11>show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

STUDENT1         BIN$49Z5BR9gDJDgRAgAJzxnug==$0 TABLE        2013-08-13:09:58:24

STUDENT2         BIN$49Z5BR9hDJDgRAgAJzxnug==$0 TABLE        2013-08-13:09:58:29

--清空回收站

scott@TESTDB11>purge recyclebin;

Recyclebin purged.

scott@TESTDB11>show recyclebin;

--

scott@TESTDB11>drop table emp1;

Table dropped.

scott@TESTDB11>show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

EMP1             BIN$49Z5BR9jDJDgRAgAJzxnug==$0 TABLE        2013-08-13:10:01:38

scott@TESTDB11>

scott@TESTDB11>select * from "BIN$49Z5BR9jDJDgRAgAJzxnug==$0";

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80       3400                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       2400        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1875        500         30

      7566 JONES      MANAGER         7839 02-APR-81     4462.5                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1875       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       4275                    30

      7788 SCOTT      ANALYST         7566 19-APR-87       4500                    20

      7844 TURNER     SALESMAN        7698 08-SEP-81       2250          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1650                    20

      7900 JAMES      CLERK           7698 03-DEC-81       1425                    30

      7902 FORD       ANALYST         7566 03-DEC-81       4500                    20

11 rows selected.

scott@TESTDB11>flashback table emp1 to before drop;

Flashback complete.

--回收站中已經沒有了

scott@TESTDB11>show recyclebin;

scott@TESTDB11>select * from emp1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80       3400                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       2400        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1875        500         30

      7566 JONES      MANAGER         7839 02-APR-81     4462.5                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1875       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       4275                    30

      7788 SCOTT      ANALYST         7566 19-APR-87       4500                    20

      7844 TURNER     SALESMAN        7698 08-SEP-81       2250          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1650                    20

      7900 JAMES      CLERK           7698 03-DEC-81       1425                    30

      7902 FORD       ANALYST         7566 03-DEC-81       4500                    20

11 rows selected.

--對於系統表空間不會有回收站

--存在於回收站中的內容會因為空間的緊張而被刪除

--回收站中有多個同名的被刪除

scott@TESTDB11>select * from emp1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80       3400                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       2400        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1875        500         30

      7566 JONES      MANAGER         7839 02-APR-81     4462.5                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1875       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       4275                    30

      7788 SCOTT      ANALYST         7566 19-APR-87       4500                    20

      7844 TURNER     SALESMAN        7698 08-SEP-81       2250          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1650                    20

      7900 JAMES      CLERK           7698 03-DEC-81       1425                    30

      7902 FORD       ANALYST         7566 03-DEC-81       4500                    20

11 rows selected.

scott@TESTDB11>drop table emp1;

Table dropped.

--出現在回收站中

scott@TESTDB11>show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

EMP1             BIN$49nL2H4iEtngRAgAJzxnug==$0 TABLE        2013-08-13:13:56:19

--再建立一個emp1表儲存不同的資料

scott@TESTDB11>create table emp1 as select * from dept;

Table created.

scott@TESTDB11>drop table emp1;

Table dropped.

scott@TESTDB11>show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

EMP1             BIN$49nL2H4jEtngRAgAJzxnug==$0 TABLE        2013-08-13:13:56:45

EMP1             BIN$49nL2H4iEtngRAgAJzxnug==$0 TABLE        2013-08-13:13:56:19

--可以在閃回之前，查詢回收站中表的具體內容

scott@TESTDB11>select * from "BIN$49nL2H4jEtngRAgAJzxnug==$0";

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

--按回收站的名稱來閃回表，避免出現歧義

scott@TESTDB11>flashback table "BIN$49nL2H4jEtngRAgAJzxnug==$0" to before drop;

Flashback complete.

scott@TESTDB11>select * from emp1;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

--只剩下一個了

scott@TESTDB11>show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

EMP1             BIN$49nL2H4iEtngRAgAJzxnug==$0 TABLE        2013-08-13:13:56:19

--把剩下的也閃回,名稱出現衝突，已經已經有物件叫EMP1了

scott@TESTDB11>flashback table "BIN$49nL2H4iEtngRAgAJzxnug==$0" to before drop;

flashback table "BIN$49nL2H4iEtngRAgAJzxnug==$0" to before drop

*

ERROR at line 1:

ORA-38312: original name is used by an existing object

--可以在閃回的過程中改名

scott@TESTDB11>flashback table "BIN$49nL2H4iEtngRAgAJzxnug==$0" to before drop rename to emp2;

Flashback complete.

--刪除時不進入回收站

scott@TESTDB11>drop table emp2 purge;

Table dropped.

scott@TESTDB11>show recyclebin;

--清除回收站中的指定內容

scott@TESTDB11>drop table emp1

  2  ;

Table dropped.

scott@TESTDB11>show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

EMP1             BIN$49nL2H4kEtngRAgAJzxnug==$0 TABLE        2013-08-13:14:27:49

scott@TESTDB11>purge table "BIN$49nL2H4kEtngRAgAJzxnug==$0";

Table purged.