[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表可能主外键的依赖也破坏,恢复时去掉主外键约束.