[20180419]關於閃回的一些問題.txt

[20180419]關於閃回的一些問題.txt

--//別人問的問題,就是drop表,然後flashbask表,建立的觸發器還在嗎?
--//我記憶裡觸發器應該還在,我個人喜歡透過例子驗證自己的判斷..

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production      0

create table t as select rownum id from dual connect by level<=10;
create or replace trigger tri_t_insert
before insert on t
for each row
begin
  null;
end;
/

2.測試:
SCOTT@test01p> drop table t ;
Table dropped.

SCOTT@test01p> flashback table t to before drop ;
Flashback complete.

SCOTT@test01p> select TRIGGER_BODY c40,TRIGGER_NAME from user_triggers where table_name='T';
C40                                      TRIGGER_NAME
---------------------------------------- --------------------
begin                                    BIN$iU6Cl+FuSHqfYa5L
  null;                                  2EGWOA==$0
end;

--//說明還在.僅僅名字修改了

3.自然會想到還有什麼情況會導致恢復後丟失什麼資訊.
--//看看約束的情況.

SCOTT@test01p> drop trigger "BIN$iU6Cl+FuSHqfYa5L2EGWOA==$0";
Trigger dropped.

SCOTT@test01p> alter table t add ( constraint ck_id check (id<11) enable validate);
Table altered.

SCOTT@test01p> insert into t values(11);
insert into t values(11)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_ID) violated

SCOTT@test01p> drop table t ;
Table dropped.

SCOTT@test01p> flashback table t to before drop ;
Flashback complete.

SCOTT@test01p> insert into t values(11);
insert into t values(11)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.BIN$bTSE9Rx6RLqZwAKVKwrnaw==$0) violated

--//約束還在,僅僅名字改了.

4.測試主外來鍵的情況:
create table p ( x int primary key );
create table c ( x references p );
insert into p values ( 1 );
insert into p values ( 2 );
commit;

SCOTT@test01p> insert into c values ( 3 );
insert into c values ( 3 )
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C0011491) violated - parent key not found

SCOTT@test01p> drop table c ;
Table dropped.

SCOTT@test01p> flashback table c to before drop ;
Flashback complete.

SCOTT@test01p> insert into c values ( 3 );
1 row created.

--//^_^,主外來鍵約束丟失.

5.12c可以支援表中使用seq,連結:http://blog.itpub.net/267265/viewspace-767751/

SCOTT@test01p> drop table t purge ;
Table dropped.

create table t
(
id number GENERATED ALWAYS as identity ( start with 1 increment by 1),
name varchar2(10)
);

insert into t (name) values('a');
commit ;

SCOTT@test01p> select * from t;
        ID NAME
---------- --------------------
         1 a

SCOTT@test01p> drop table t ;

Table dropped.

SCOTT@test01p> flashback table t to before drop ;
Flashback complete.        

SCOTT@test01p> select * from t;
        ID NAME
---------- --------------------
         1 a
         2 b
--//沒有問題.現在看來僅僅在主外來鍵定義上丟失.
--//可以發現oracle設計時考慮問題的周到,因為drop c表後如果p表dml操作導致主外來鍵的依賴可能失效,
--//比如delete from p where x=1.這樣即使閃回c表可能主外來鍵的依賴也破壞,恢復時去掉主外來鍵約束.