flashback table后,怎样恢复和表相关的对象

By on 2009/02/03 at 12:00 上午 in Oracle RDBMS

本文标题:flashback table后,怎样恢复和表相关的对象【本站如从外站转载,会注明出处】
本文链接:http://www.validba.net/2009/02/flashback-table%e5%90%8e%e6%80%8e%e6%a0%b7%e6%81%a2%e5%a4%8d%e5%92%8c%e8%a1%a8%e7%9b%b8%e5%85%b3%e7%9a%84%e5%af%b9%e8%b1%a1/

测试目的: flashback table后,怎样恢复跟表相关的约束和对象

四张表 test1,test2,test3,test4
 
       test1上有一个trigger
       view cquery 和test1相关
       metarialized view m_query和test1相关
       test2和test1存在主外键关系 FK_TEST1_FID_REF_TEST2_ID
       test1和test4存在主外外键 fk_test4_reference_test1

本次测试为:删除test1,并flashback table test1,然后恢复跟test1相关的对象.

测试前首先删除test4上的外键,因为他应用了test1的主键

alter table test4 modify constraint fk_test4_reference_test1 disable novalidate;
alter table test4 drop constraint fk_test4_reference_test1;

先禁用后删除了也是为了能够安全删除该外键约束.

 

1、删除表test1,观察跟他相关联的所有对象的状态

删除表test1

   SQL> drop table test1;

   SQL> show recyclebin
    ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
    —————- —————————— ———— ——————-
    TEST1            BIN$WdOnMF09VLfgQKjA1MtsHA==$0 TABLE        2008-10-22:16:55:35
    
    test1已经回到了recyclebin

   观察跟test1相关的trigger
   
    SQL> select trigger_name,table_name from user_triggers;

    TRIGGER_NAME                   TABLE_NAME
     —————————– ——————————
     BIN$WdOnMF08VLfgQKjA1MtsHA==$0 BIN$WdOnMF09VLfgQKjA1MtsHA==$0

    trigger被改名

   观察约束

    SQL> select table_name,constraint_name,constraint_type,r_constraint_name from user_constraints;

     TABLE_NAME                     CONSTRAINT_NAME                C R_CONSTRAINT_NAME
     —————————— —————————— – ——————————
     TEST4                          SYS_C005293                    C
     TEST2                          PK_TEST2_ID                    P
     TEST4                          PK_TEST4_ID                    P
     BIN$WdOnMF09VLfgQKjA1MtsHA==$0 BIN$WdOnMF06VLfgQKjA1MtsHA==$0 P

     注意这里主键也被改名了,而外键不存在了.

      观察索引
   
     SQL> select index_name from user_indexes;

      INDEX_NAME
       ——————————
       PK_TEST2_ID_IDX
       PK_TEST4_ID_IDX
       BIN$WdOnMF07VLfgQKjA1MtsHA==$0
  
       这里的主键索引已经被改名了.

      观察materialized

     SQL> select mview_name,compile_state from user_mviews;

      MVIEW_NAME                     COMPILE_STATE
      —————————— ——————-
      M_QUERY                        NEEDS_COMPILE
       
      但刷新有问题了,因为它需要的表test1被删除了

      SQL> exec dbms_mview.refresh(‘M_QUERY’);
      BEGIN dbms_mview.refresh(‘M_QUERY’); END;

       *
      ERROR at line 1:
      ORA-00942: table or view does not exist
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
       ORA-06512: at line 1

      观察view

      view cquery 没有什么可观察的,肯定处于invalid状态了.
 
2、flashback test1
      
     SQL> flashback table test1 to before drop;
     Flashback complete.
      表已经被正常恢复了
 
     观察约束恢复情况
     SQL> select table_name,constraint_name,constraint_type,r_constraint_name from user_constraints;
     TABLE_NAME                     CONSTRAINT_NAME                C R_CONSTRAINT_NAME
      —————————— —————————— – ——————————
      TEST4                          SYS_C005293                    C
      TEST2                          PK_TEST2_ID                    P
      TEST4                          PK_TEST4_ID                    P
      TEST1                          BIN$WdOnMF06VLfgQKjA1MtsHA==$0 P
 
      主键约束没有恢复,需要手动改名,
      SQL> alter table test1 rename constraint "BIN$WdOnMF06VLfgQKjA1MtsHA==$0" to PK_TEST1_ID;
      Table altered.
      
       外键没有恢复,只有重建了
      
       SQL> alter table test1 add constraint fk_test1_fid_from_t2_id foreign key (f_id) references  test2(id);
      Table altered.
 
       
      观察索引恢复情况
       SQL> select index_name from user_indexes;
        INDEX_NAME
         ——————————
         PK_TEST2_ID_IDX
         PK_TEST4_ID_IDX
         BIN$WdOnMF07VLfgQKjA1MtsHA==$0
        
        索引没有还原,看看索引的执行计划
 
        SQL> select id,name from test1 where id = 1;
        ID NAME
         ———- ——————————
         1 mayp
        Execution Plan
         ———————————————————-
         Plan hash value: 2102021671
         ————————————————————————————————————–
          | Id  | Operation                   | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
          ————————————————————————————————————–
          |   0 | SELECT STATEMENT            |                                |     1 |     9 |     1   (0)| 00:00:01 |
          |   1 |  TABLE ACCESS BY INDEX ROWID| TEST1                          |     1 |     9 |     1   (0)| 00:00:01 |
          |*  2 |   INDEX UNIQUE SCAN         | BIN$WdOnMF07VLfgQKjA1MtsHA==$0 |     1 |       |     0   (0)| 00:00:01 |
          ————————————————————————————————————–
      
          
         根据执行计划可以看出,只是索引改名了,但对索引本身没有什么破坏.
         只要对索引改名就可以了
         SQL> alter index "BIN$WdOnMF07VLfgQKjA1MtsHA==$0" rename to PK_TEST1_ID_IDX;
         Index altered.
         SQL> select index_name,index_type from user_indexes;
         INDEX_NAME                     INDEX_TYPE
          —————————— —————————
         PK_TEST2_ID_IDX                NORMAL
         PK_TEST4_ID_IDX                NORMAL
         PK_TEST1_ID_IDX                NORMAL
 
     观察trigger是否恢复
 
         SQL> select table_name,trigger_name,status from user_triggers;
         TRIGGER_NAME                   STATUS
         —————————— —————————— ——–
         TEST1                          BIN$WdOnMF08VLfgQKjA1MtsHA==$0 ENABLED
         跟索引一样,还是没有还原,需要手动改名
         SQL> alter trigger "BIN$WdOnMF08VLfgQKjA1MtsHA==$0" rename to INSERT_TEST3_FROM_TEST1;
         Trigger altered.
 
         重新刷新materialized view,重新编译view cquery
         SQL> exec dbms_mview.refresh(‘M_QUERY’);
          PL/SQL procedure successfully completed.
          SQL> alter view cquery compile;
          View altered. 
 
         只此所有相关的对象都被还原了. 
 
         总结:
      1、flashback table只能恢复表定义和数据,对于表的附加对象比如trigger、主键、索引,在表被drop后,orale并不会 物理删除这些对象,而是将这些对象     rename了,在表flashback后, 手动将这些对象重新rename或者删除重建;但除了主键约束外其他的约束都会被删除,需要重建恢复,为什么主键没有被删除?因为主键所关联的唯一性索引没有删除,所以主键不会被删除;
      2、应用该表的对象,比如 materialized view、view、procedure、function会变为invalid,表恢复后重新编译就可以了.

CopyRight Validba'S Home 版权所有.本站为个人学习之用,评论时请遵守本国法律

Relative Posts

Tags:

Reader's Comments »

  1. By Armena on 2011/09/26 at 7:01 下午

    Unaprallleed accuracy, unequivocal clarity, and undeniable importance!

Leave a Reply