Oracle故障分析：启用与禁用表的约束是否会导致存储过程无效
客户一套系统因为library cache pin/library cache lock导致整个系统hang住了，全部业务中断，通过分析过，是由于人为导致编译了一个子的存储过程，导致父存储过程在执行时发现无效，自动进行编译，然而存储过程切一直在执行，最后导致了整个业务系统的hang住，这个存储过程是一个接口，所有的应用都要调用，业务通过分析日志，切没有找到任何的alter procedure的语句，只发现了alter table *** disable/enable constraint语句，所以怀疑我们的分析结果，下面是一个测试结果，让业务方知道约束的启用与禁用是不会导致过程失效的。测试环境：DB:RHEL 4.8 OS:10.2.0.4.12
1，创建测试环境：
www.htz.pw >grant dba to scott;
Grant succeeded.
www.htz.pw >create table scott.test2(id number,id2 varchar2(20));
Table created.
www.htz.pw >alter table scott.test2 add constraint pk_test2_id primary key(id);
Table altered.
www.htz.pw >create table scott.test3(id number,id2 varchar2(20));
Table created.
www.htz.pw >alter table scott.test3 add constraint for_test3_id foreign key(id) references scott.test2(id);
Table altered.
www.htz.pw >conn scott/oracle
Connected.
www.htz.pw >create or replace procedure test_proc
2 is
3 begin
4 execute immediate 'select count(*) from scott.test2';
5 execute immediate 'select count(*) from scott.test3';
6 end;
7 /
Procedure created.
create or replace procedure test_proc2 is
begin
scott.test_proc;
end;
/2，禁用约束
www.htz.pw >alter table scott.test3 disable constraint for_test3_id;
Table altered.
www.htz.pw >alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
www.htz.pw >select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name in ('TEST2','TEST3','TEST_PROC','TEST_PROC2') and owner='SCOTT';
OWNER OBJECT_NAME OBJECT_TYPE LAST_DDL_TIME STATUS
------------------------------ ------------------------------ ------------------- ------------------- -------
SCOTT TEST2 TABLE 2013-06-21 09:52:55 VALID
SCOTT TEST3 TABLE 2013-06-21 10:09:09 VALID
SCOTT TEST_PROC PROCEDURE 2013-06-21 09:53:30 VALID
SCOTT TEST_PROC2 PROCEDURE 2013-06-21 10:07:52 VALID
这里发现过程是有效的
再禁用另一个约束
www.htz.pw >alter table scott.test2 disable constraint pk_test2_id;
Table altered.
www.htz.pw >select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name in ('TEST2','TEST3','TEST_PROC','TEST_PROC2') and owner='SCOTT';
OWNER OBJECT_NAME OBJECT_TYPE LAST_DDL_TIME STATUS
------------------------------ ------------------------------ ------------------- ------------------- -------
SCOTT TEST2 TABLE 2013-06-21 10:10:08 VALID
SCOTT TEST3 TABLE 2013-06-21 10:09:09 VALID
SCOTT TEST_PROC PROCEDURE 2013-06-21 09:53:30 VALID
SCOTT TEST_PROC2 PROCEDURE 2013-06-21 10:07:52 VALID
两个过程都是有效的3，启用约束
www.htz.pw >alter table scott.test2 enable constraint pk_test2_id;
Table altered.
www.htz.pw >alter table scott.test3 enable constraint for_test3_id;
Table altered.
www.htz.pw >select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name in ('TEST2','TEST3','TEST_PROC','TEST_PROC2') and owner='SCOTT';
OWNER OBJECT_NAME OBJECT_TYPE LAST_DDL_TIME STATUS
------------------------------ ------------------------------ ------------------- ------------------- -------
SCOTT TEST2 TABLE 2013-06-21 10:10:46 VALID
SCOTT TEST3 TABLE 2013-06-21 10:11:11 VALID
SCOTT TEST_PROC PROCEDURE 2013-06-21 09:53:30 VALID
SCOTT TEST_PROC2 PROCEDURE 2013-06-21 10:07:52 VALID通过上面的测试可以发现，禁用约束并不会影响过程的有效性与last_ddl_time的值。
------------------作者介绍-----------------------
姓名：黄廷忠
