浅谈Oracle闪回删除表限制
浅谈Oracle闪回删除表限制
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
|
评论暂时关闭