Oracle 10g开始提供了类似windows系统的回收站功能,用户在删除表的时候会不是直接删除,而是移动到回收站中,如果需要从回收站中取回原来的表,可以使用闪回删除表的特性,迅速的找回被删除的表,而不需要从备份中导入原有的表!但这个回收站功能也是有前提的,不是在任何情况下都可以使用闪回删除表特性,总结下,在10g中,下面几种场景表不能flashback删除,至于11g是否有改进,感兴趣的朋友可以参照本文的方法进行测试下!顺带说下,回收站采取fifo,先进先出的机制!例如在回收站有两张同样名称的表,闪回删除的时候根据删除时间的先后顺序来觉得先闪回哪张表,因而闪回删除表命令提供了rename to选项;
1:回收站功能未开启(这个就不用测试了)
2:表的存储表空间不能为system
3: 表被删除的时候不能带purge参数
4:存在空间压力的时候
5:表上面启用了细粒度审计
6:表启用了VPD
一:测试表空间存储为system的闪回删除
1.1 创建测试用户,并赋予相应的权限,开启数据库的回收站功能
[oracle@dg53 ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 16 16:48:44 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn /as sysdba Connected. SQL> create user test identified by "123456" default tablespace users account unlock; User created. SQL> grant resource,connect to test; Grant succeeded. SQL> show parameter recyclebin; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on
1.2 使用test用户新建2张表,一张存储在system表空间
Connected. SQL> create table drop_1 (a number) tablespace system; Table created. SQL> insert into drop_1 values (1); 1 row created. SQL> commit; Commit complete. SQL> create table drop_2 as select * from drop_1; Table created. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DROP_1 TABLE DROP_2 TABLE
1.3 分别删除2张表,不带purge参数,查看回收站只存在drop_2表,该表的存储表空间为users,由此证明表空间存储为system的表是不可以被闪回删除的
SQL> drop table drop_1; Table dropped. SQL> drop table drop_2; Table dropped. SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- DROP_2 BIN$uRD2vL3ZVNjgQKjANQEaNg==$0 TABLE 2012-02-16:16:53:36 SQL> select object_name,original_name from user_recyclebin; OBJECT_NAME ORIGINAL_NAME ------------------------------ -------------------------------- BIN$uRD2vL3ZVNjgQKjANQEaNg==$0 DROP_2 SQL> flashback table drop_2 to before drop; Flashback complete.
1.4 测试使用sys用户删除drop_2表,是否可以成功闪回删除
SQL> conn /as sysdba Connected. SQL> show user; USER is "SYS" SQL> drop table test.drop_2; Table dropped. SQL> conn test/123456 Connected. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BIN$uRImQA9UYD7gQKjANQEdrg==$0 TABLE SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- DROP_2 BIN$uRImQA9UYD7gQKjANQEdrg==$0 TABLE 2012-02-16:17:06:54 SQL> flashback table drop_2 to before drop; Flashback complete