[20180423]關於閃回表與主外來鍵約束.txt

[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 ;時實際上主外來鍵約束就已經刪除了.