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.