[20180423]关于闪回表与主外键约束.txt

--//上个星期做的测试,可以发现删除表在恢复主外键约束丢失.

--//链接: --//不过这个可以模拟一个另外的测试.

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 ;时实际上主外键约束就已经删除了.