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