flashback六大技术之flashback drop

长平狐 发布于 2012/09/19 13:55
阅读 55
收藏 0

环境:

sys@ORCL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

sys@ORCL> !uname -a
Linux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux


    1 recyclebin

 

         在oracle 10g引入了recyclebin,对于一个对象的删除,oracle先通过修改数据字典,将其及其关联对象(索引、约束等)重命名,然后放入recyclebin。被删除的对象将占用创建时的同样的空间大小,当出现空间压力时,这个空间才会被慢慢回收。但是当对象被删除之后,这部分空间会计入free space,被看作是自由空间,可重用,在dba_free_space可查。如果能够确认删除对象,则可以使用purge命令完全删除,这样可以减少动态空间回收带来的性能代价。

 

         每个用户都有属于自己的一个recyclebin。查看recyclebin的方法很多,最常用的是select * from recyclebin;最简单的是show recyclebin。需要注意的是show recyclebin只列出基表,被删除的表的关联对象则不显示。recyclebin里对象的名称也可以被当做普通名称一样使用,唯一区别是,无法被rename。

 

          ● 表空间无足够的空闲空间,并且没有新的空间可作扩展操作
          ● 该表空间又要创建新的对象,需要分配空间
          在这种情形下就会造成recyclebin的空间压力,这是触发recyclebin主动删除对象的唯一原因。

 

           清空recyclebin中对象
           ㈠ 修改初始化参数禁用或启用recyclebin,会话级或者实例级皆可。
                 缺省该参数是on

hr@ORCL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
hr@ORCL> create table tt as select * from jobs;

Table created.

hr@ORCL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
hr@ORCL> alter session set recyclebin=off;

Session altered.

hr@ORCL> drop table tt;

Table dropped.

hr@ORCL> select * from recyclebin;

no rows selected


         ㈡ drop ..purge;
         ㈢ 清空表空间现有对象
              方式有三:
                             purge table original name或者recyclebin name
                             purge tablespace tbs_name
                             purge recylebin

 

    2 注意事项

 

             ① flashback drop不能恢复参照完整性(即主外键关系),恢复之后,该约束为disable状态,需手动处理
             ② 所操作的表必须是本地表空间管理
             ③ 被恢复的表的关联对象(索引、约束等),其名称不会自动恢复成删除前的名称,而是系统自动生成。需DBA手动改名。另外,位图索引、物化视图不能被恢复。
             ④ 当空间不足时,被删除表的索引会优先被清理
             ⑤ flashback drop支持同时操作多个表,以逗号分隔
             ⑥ flashback drop只能恢复drop命令删除的表

 

    3 实验

      

         ㈠ 最简单的删除表恢复

hr@ORCL> drop table t2;

Table dropped.

hr@ORCL> select object_name,original_name from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$yUcFsAG7oGvgQAB/AQAjTg==$0 T2

hr@ORCL> flashback table t2 to before drop;

Flashback complete.

hr@ORCL> select count(*) from t2;

  COUNT(*)
----------
        19

hr@ORCL> select object_name,original_name from recyclebin;

no rows selected


         ㈡ 稍微复杂一点点的表恢复
               如果要恢复的表在当前schema中已经存在同名的表,直接恢复会报错

hr@ORCL> drop table t2;

Table dropped.

hr@ORCL> create table t2 as select * from jobs;

Table created.

hr@ORCL> flashback table t2 to before drop;
flashback table t2 to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object


hr@ORCL> flashback table t2 to before drop rename to t2_recov;

Flashback complete.

hr@ORCL> select count(*) from t2_recov;

  COUNT(*)
----------
        19


          ㈢ 从多次删除中恢复
               多个同名表一起进recyclebin,后进先出

hr@ORCL> drop table t2;

Table dropped.
hr@ORCL> alter table t2_recov rename to t2;

Table altered.

hr@ORCL> drop table t2;

Table dropped.

hr@ORCL> select object_name,original_name,droptime from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME
------------------------------ -------------------------------- -------------------
BIN$yUcFsAG/oGvgQAB/AQAjTg==$0 T2                               2012-09-09:23:47:12
BIN$yUcFsAHBoGvgQAB/AQAjTg==$0 T2                               2012-09-09:23:48:42

hr@ORCL> flashback table t2 to before drop;

Flashback complete.

hr@ORCL> select object_name,original_name,droptime from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME
------------------------------ -------------------------------- -------------------
BIN$yUcFsAG/oGvgQAB/AQAjTg==$0 T2                               2012-09-09:23:47:12

hr@ORCL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
REGIONS                        TABLE
COUNTRIES                      TABLE
LOCATIONS                      TABLE
DEPARTMENTS                    TABLE
JOBS                           TABLE
EMPLOYEES                      TABLE
JOB_HISTORY                    TABLE
EMP_DETAILS_VIEW               VIEW
LOGMNR_TEST                    TABLE
BIN$yUcFsAG/oGvgQAB/AQAjTg==$0 TABLE
T2                             TABLE

11 rows selected.

hr@ORCL> drop table t2;

Table dropped.

hr@ORCL> select object_name,original_name,droptime from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME
------------------------------ -------------------------------- -------------------
BIN$yUcFsAHDoGvgQAB/AQAjTg==$0 T2                               2012-09-09:23:52:39
BIN$yUcFsAG/oGvgQAB/AQAjTg==$0 T2                               2012-09-09:23:47:12

hr@ORCL> flashback table "BIN$yUcFsAG/oGvgQAB/AQAjTg==$0" to before drop;

Flashback complete.

hr@ORCL> select count(*) from t2;

  COUNT(*)
----------
        19

hr@ORCL> select object_name,original_name,droptime from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME
------------------------------ -------------------------------- -------------------
BIN$yUcFsAHDoGvgQAB/AQAjTg==$0 T2                               2012-09-09:23:52:39


 


原文链接:http://blog.csdn.net/linwaterbin/article/details/7961784
加载中
返回顶部
顶部