--創建一個表，此表作為子表
create table fk_t as select *from user_objects;
delete from fk_t where object_id is null;
commit;
--創建一個表，此表作為父表
create table pk_t as select *from user_objects;
delete from pk_t where object_id is null;
commit;
--創建父表的主鍵
alter table PK_t add constraintpk_pktable primary key (OBJECT_ID);
--創建子表的外鍵
alter table FK_t addconstraint fk_fktable foreign key (OBJECT_ID) references pk_t (OBJECT_ID);
--session1:執行一個刪除操作，這時候在子表和父表上都加了一個Row-S(SX)鎖
delete from fk_t whereobject_id=100;
delete from pk_t where object_id=100;
--session2:執行另一個刪除操作，發現這時候第二個刪除語句等待
delete from fk_t whereobject_id=200;
delete from pk_t whereobject_id=200;
--回到session1:死鎖馬上發生
delete from pk_t whereobject_id=100;
session2中報錯：
SQL> delete from pk_table where object_id=200;
delete from pk_table where object_id=200
*
第 1 行出現錯誤:
ORA-00060: 等待資源時檢測到死鎖
當對子表的外鍵列添加索引後，死鎖被消除，因為這時刪除父表記錄不需要對子表加表級鎖。
--為外鍵建立索引
create index ind_pk_object_id on fk_t(object_id) nologging;
--重復上面的操作session1
delete from fk_t whereobject_id=100;
delete from pk_t whereobject_id=100;
--session2
delete from fk_t whereobject_id=200;
delete from pk_t whereobject_id=200;
--回到session1不會發生死鎖
delete from pk_t whereobject_id=100;
