[20180423]关于闪回表与主外键约束.txt
--//上个星期做的测试,可以发现删除表在恢复主外键约束丢失.
--//链接:http://blog.itpub.net/267265/viewspace-2153199/
--//不过这个可以模拟一个另外的测试.
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
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@book> insert into c values ( 3 );
insert into c values ( 3 )
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C0011835) violated - parent key not found
2.测试1:
--//session 1:
SCOTT@book> insert into p values ( 3 );
1 row created.
--//session 2:
SCOTT@book> drop table c purge;
drop table c purge
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SCOTT@book> drop table c ;
drop table c
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
--//因为P表存在insert操作,而这个要锁定c表获取TM锁,这样删除是无法操作的:
SCOTT@book> @ &r/viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
274 9 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 90716 0 SCOTT TABLE C No
274 9 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 655387 21831 No
274 9 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 90714 0 SCOTT TABLE P No
3.测试2:
--//session 1:
SCOTT@book> commit ;
Commit complete.
--//注意不提交.
SCOTT@book> update p set x=x where x=2;
1 row updated.
--//session 2:
SCOTT@book> @ &r/viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
274 9 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 655370 21844 No
274 9 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 90714 0 SCOTT TABLE P No
--//有点奇怪这个时候并没有锁C表.
SCOTT@book> insert into c values (2);
1 row created.
SCOTT@book> commit ;
Commit complete.
--//c表可以进行正常插入操作.
--//session 1:
SCOTT@book> commit ;
Commit complete.
SCOTT@book> delete from c ;
1 row deleted.
SCOTT@book> commit ;
Commit complete.
4.测试3:
--//session 1:
--//注意不提交.
SCOTT@book> @ &r/s
SCOTT@book(274,9)>
SCOTT@book> update p set x=x+2 where x=2;
1 row updated.
--//session 2:
SCOTT@book> @ &r/s
SCOTT@book(106,69)>
SCOTT@book> insert into c values (2);
--//挂起!!
SCOTT@book> @ &r/viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
106 69 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 90716 0 SCOTT TABLE C No 0000000084C36170
106 69 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 90714 0 SCOTT TABLE P No 0000000084C36170
106 69 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 589841 2823 No 0000000084C36170
106 69 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction None Share 655384 21828 No 0000000084C36170
274 9 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 655384 21828 Yes
274 9 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 90714 0 SCOTT TABLE P No
6 rows selected.
--//偏题了,不再做主外键测试.
5.测试5:
--//session 1:
SCOTT@book(274,9)> select * from p;
X
----------
1
2
3
SCOTT@book(274,9)> select * from c;
no rows selected
SCOTT@book(274,9)> update p set x=x where x=2;
1 row updated.
--//session 2:
SCOTT@book(106,69)> @ &r/viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
274 9 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 655377 21680 No
274 9 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 90714 0 SCOTT TABLE P No
SCOTT@book(106,69)> drop table c purge;
drop table c purge
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
--//不能删除表C.
SCOTT@book(106,69)> drop table c ;
Table dropped.
--//这样实际上并没有真正删除,而是进入回收站.
SCOTT@book(106,69)> flashback table c to before drop ;
Flashback complete.
SCOTT@book(106,69)> insert into c values (1111);
1 row created.
SCOTT@book(106,69)> commit ;
Commit complete.
--//恢复后主外键已经破坏,可以插入异常数据.
--//可以看出一种特殊的情况就是drop table c ;时实际上主外键约束就已经删除了.
