查询回收站中的已经被DROP的表:

SQL> select * from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE
------------------------------ -------------------------------- --------- ------
BIN$NZPa3St6RiG/uVVGZZztgg==$0 EMP2                             DROP      TABLE
BIN$9v30fwxzThuhaRHFKTMxZg==$0 EMP2                             DROP      TABLE
BIN$FjmRqNJDRw6FaeSU/u+XBw==$0 EMPNO_HIRETDAE_INDEX             DROP      INDEX
BIN$F9KQqNcQTfapxWTCDt+Z9A==$0 DEPT2                            DROP      TABLE
BIN$Quq78zXYRUu7K0SkpkOIBA==$0 T1                               DROP      TABLE
BIN$e+lZ+TgsQCWMVxVkkBTXPA==$0 T2                               DROP      TABLE
BIN$345imaJERE6TSwP6ACxAMA==$0 ASTRON_VIEW_SCRIPT               DROP      TABLE
BIN$6PFxiKfOSxSQHd5tTcr80g==$0 ASTRON_VIEW_SCRIPT               DROP      TABLE
BIN$fWKi3sthTAK4TkUFiqmHuA==$0 ASTRON_VIEW_SCRIPT               DROP      TABLE
SYS_LOB0000057016C00002$$      SYS_LOB0000057016C00002$$        DROP      LOB
SYS_IL0000057016C00002$$       SYS_IL0000057016C00002$$         DROP      LOB IN

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE
------------------------------ -------------------------------- --------- ------
BIN$SMtnNzZGQJy8/9CBUrJ0lA==$0 EMP_NOLOG                        DROP      TABLE

已选择12行。

SQL> show user
USER 为 "SCOTT"
SQL> select * from BIN$Quq78zXYRUu7K0SkpkOIBA==$0;
select * from BIN$Quq78zXYRUu7K0SkpkOIBA==$0
                                        *
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束

SQL> select * from "BIN$Quq78zXYRUu7K0SkpkOIBA==$0";

TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME
------------------------------ ------------------------------ ------------------
DEPT                           USERS
EMP                            USERS
BONUS                          USERS
SALGRADE                       USERS
EMP3                           USERS

由于表名再数据字典表已经被清除,只能使用回收站的对象名来查询被删除表的数据

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
ASTRON_VIEW_SCRI BIN$345imaJERE6TSwP6ACxAMA==$0 TABLE        2008-01-16:12:01:41
ASTRON_VIEW_SCRI BIN$6PFxiKfOSxSQHd5tTcr80g==$0 TABLE        2008-01-16:11:53:08
ASTRON_VIEW_SCRI BIN$fWKi3sthTAK4TkUFiqmHuA==$0 TABLE        2008-01-16:11:19:38
DEPT2            BIN$F9KQqNcQTfapxWTCDt+Z9A==$0 TABLE        2008-01-18:21:07:24
EMP2             BIN$NZPa3St6RiG/uVVGZZztgg==$0 TABLE        2008-01-18:21:44:59
EMP2             BIN$9v30fwxzThuhaRHFKTMxZg==$0 TABLE        2008-01-18:21:08:29
EMP_NOLOG        BIN$SMtnNzZGQJy8/9CBUrJ0lA==$0 TABLE        2007-12-30:11:01:26
T1               BIN$Quq78zXYRUu7K0SkpkOIBA==$0 TABLE        2008-01-18:21:06:08
T2               BIN$e+lZ+TgsQCWMVxVkkBTXPA==$0 TABLE        2008-01-18:21:04:26
SQL> select * from "BIN$Quq78zXYRUu7K0SkpkOIBA==$0";

TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME
------------------------------ ------------------------------ ------------------
DEPT                           USERS
EMP                            USERS
BONUS                          USERS
SALGRADE                       USERS
EMP3                           USERS

SQL> drop table "BIN$Quq78zXYRUu7K0SkpkOIBA==$0";
drop table "BIN$Quq78zXYRUu7K0SkpkOIBA==$0"
           *
第 1 行出现错误:
ORA-38301: 无法对回收站中的对象执行 DDL/DML

对回收站表不能做删除操作

SQL> show parameter recycle

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle                  string
db_recycle_cache_size                big integer 0
recyclebin                           string      on

SQL> conn / as sysdba
已连接。
SQL> alter system set recyclebin=off;

系统已更改。

SQL> conn scott/tiger
已连接。
SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
ASTRON_VIEW_SCRI BIN$345imaJERE6TSwP6ACxAMA==$0 TABLE        2008-01-16:12:01:41
ASTRON_VIEW_SCRI BIN$6PFxiKfOSxSQHd5tTcr80g==$0 TABLE        2008-01-16:11:53:08
ASTRON_VIEW_SCRI BIN$fWKi3sthTAK4TkUFiqmHuA==$0 TABLE        2008-01-16:11:19:38
DEPT2            BIN$F9KQqNcQTfapxWTCDt+Z9A==$0 TABLE        2008-01-18:21:07:24
EMP2             BIN$NZPa3St6RiG/uVVGZZztgg==$0 TABLE        2008-01-18:21:44:59
EMP2             BIN$9v30fwxzThuhaRHFKTMxZg==$0 TABLE        2008-01-18:21:08:29
EMP_NOLOG        BIN$SMtnNzZGQJy8/9CBUrJ0lA==$0 TABLE        2007-12-30:11:01:26
T1               BIN$Quq78zXYRUu7K0SkpkOIBA==$0 TABLE        2008-01-18:21:06:08
T2               BIN$e+lZ+TgsQCWMVxVkkBTXPA==$0 TABLE        2008-01-18:21:04:26
SQL> select * from "BIN$Quq78zXYRUu7K0SkpkOIBA==$0";

TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME
------------------------------ ------------------------------ ------------------
DEPT                           USERS
EMP                            USERS
BONUS                          USERS
SALGRADE                       USERS
EMP3                           USERS

即使关闭回收站功能,回收站原有的数据保留;

假如建立一个emp2表,drop table emp2.然后又建立emp2,如果这个也drop掉,

当执行flashback drop会怎么样呢?

SQL> delete from emp2;

已删除14行。

SQL> commit;

提交完成。

SQL> select * from emp2;
未选定行

SQL> flashback table emp2 to timestamp systimestamp - interval '15' minute;
flashback table emp2 to timestamp systimestamp - interval '15' minute
                *
第 1 行出现错误:
ORA-08189: 因为未启用行移动功能, 不能闪回表

SQL> alter table emp2 enable row movement;

表已更改。

SQL> flashback table emp2 to timestamp - interval '15' minute;
flashback table emp2 to timestamp - interval '15' minute
                                    *
第 1 行出现错误:
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 INTERVAL DAY TO SECOND

SQL> flashback table emp2 to timestamp systimestamp - interval '15' minute;

闪回完成。

SQL>
SQL> select * from emp2;
行将被截断

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
      7566 JONES      MANAGER         7839 02-4月 -81           2975
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850
      7782 CLARK      MANAGER         7839 09-6月 -81           2450
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000
      7839 KING       PRESIDENT            17-11月-81           5000
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
      7876 ADAMS      CLERK           7788 23-5月 -87           1100

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
      7900 JAMES      CLERK           7698 03-12月-81            950
      7902 FORD       ANALYST         7566 03-12月-81           3000
      7934 MILLER     CLERK           7782 23-1月 -82           1300

已选择14行。

SQL> drop table emp2;

表已删除。

SQL>
SQL>
SQL> flashback table emp2 to timestamp systimestamp -interval '15' minute;
flashback table emp2 to timestamp systimestamp -interval '15' minute
                *
第 1 行出现错误:
ORA-00942: 表或视图不存在

SQL> flashback table emp2 to before drop;

闪回完成。

SQL> select * from emp2;
行将被截断

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
      7566 JONES      MANAGER         7839 02-4月 -81           2975
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850
      7782 CLARK      MANAGER         7839 09-6月 -81           2450
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000
      7839 KING       PRESIDENT            17-11月-81           5000
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
      7876 ADAMS      CLERK           7788 23-5月 -87           1100

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
      7900 JAMES      CLERK           7698 03-12月-81            950
      7902 FORD       ANALYST         7566 03-12月-81           3000
      7934 MILLER     CLERK           7782 23-1月 -82           1300
      7369 SMITH      CLERK           7902 17-12月-80            800
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
      7566 JONES      MANAGER         7839 02-4月 -81           2975
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850
      7782 CLARK      MANAGER         7839 09-6月 -81           2450
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
      7839 KING       PRESIDENT            17-11月-81           5000
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
      7876 ADAMS      CLERK           7788 23-5月 -87           1100
      7900 JAMES      CLERK           7698 03-12月-81            950
      7902 FORD       ANALYST         7566 03-12月-81           3000
      7934 MILLER     CLERK           7782 23-1月 -82           1300

已选择28行。

经过测试发现,如果删除两次同名的表,结构相同,
当执行flashback drop时,会把两次的记录都放到这个表里