问题:
如果表中数据量太大,在创建外键时将耗费较长时间(阻塞子表/父表上的正常dml操作).优化的方案是分两步执行：
第一步先创建novalidate的外键，可以在很短时间内完成；
第二步将该外键validate，虽然耗时较长，由于并不持有表上的TM锁，并不阻塞正常dml操作。

两种情况下获取TM锁情况如下。

版本:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

--1,测试准备

create table p(x int);

alter table p
add constraint pk_p primary key(x);

insert into p values(1);
insert into p values(2);
insert into p values(3);

commit;

create table c(x int);
insert into c values(1);
commit;

select object_name,object_type,object_id,to_char(object_id,'xxxxxxx') from user_objects;
OBJECT_NAM OBJECT_TYPE          OBJECT_ID TO_CHAR(
---------- ------------------- ---------- --------
P          TABLE                   156099    261c3
PK_P       INDEX                   156100    261c4
C          TABLE                   156101    261c5

--2,直接加外键情况
alter session set events '10704 trace name context forever,level 12';
alter table c
add constraint fk_c_r_p foreign key(x) references p(x);

alter session set events '10704 trace name context off';

--bnet_ora_3434.trc
*** 2028-09-20 13:47:54.648
ksqgtl *** TM-000261c5-00000000 mode=4 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x0x3db4eac4, ktcdix=2147483647, topxcb=0x0x3db4eac4
ktcipt(topxcb)=0x0
*** 2028-09-20 13:47:54.651
ksqgtl *** TM-000261c3-00000000 mode=4 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x0x3db4eac4, ktcdix=2147483647, topxcb=0x0x3db4eac4
ktcipt(topxcb)=0x0

*** 2028-09-20 13:47:54.706
ksqrcl: TM,261c3,0
ksqrcl: returns 0
*** 2028-09-20 13:47:54.706
ksqrcl: TM,261c5,0
ksqrcl: returns 0
==>首先获取子表mode=4的TM锁,随后获取父表mode=4的TM锁

--3,直接加外键情况
alter table c
drop constraint fk_c_r_p;

alter session set events '10704 trace name context forever,level 12';
alter table c
add constraint fk_c_r_p foreign key(x) references p(x) enable novalidate;

alter session set events '10704 trace name context off';
--bnet_ora_7399.trc
*** 2028-09-20 14:12:13.901
ksqgtl *** TM-000261c5-00000000 mode=4 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x0x3dbd9ef0, ktcdix=2147483647, topxcb=0x0x3dbd9ef0
ktcipt(topxcb)=0x0
*** 2028-09-20 14:12:13.918
ksqgtl *** TM-000261c3-00000000 mode=4 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x0x3dbd9ef0, ktcdix=2147483647, topxcb=0x0x3dbd9ef0
ktcipt(topxcb)=0x0
*** 2028-09-20 14:12:13.949
ksqrcl: TM,261c3,0
ksqrcl: returns 0
*** 2028-09-20 14:12:13.949
ksqrcl: TM,261c5,0
ksqrcl: returns 0
==>首先获取子表mode=4的TM锁,随后获取父表mode=4的TM锁

alter session set events '10704 trace name context forever,level 12';
alter table c
modify constraint fk_c_r_p enable validate;

alter session set events '10704 trace name context off';

--bnet_ora_8111.trc
==>没有获取子表/父表的TM锁

ITPub博客

维护表上外键持有的TM锁

最新文章