认真就输

      客户一套系统因为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的值。