当前位置: 首页 > BASIC > 正文

启用与禁用表的约束是否会导致存储过程无效

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

本文固定链接: http://www.htz.pw/2013/06/30/%e5%90%af%e7%94%a8%e4%b8%8e%e7%a6%81%e7%94%a8%e8%a1%a8%e7%9a%84%e7%ba%a6%e6%9d%9f%e6%98%af%e5%90%a6%e4%bc%9a%e5%af%bc%e8%87%b4%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e6%97%a0%e6%95%88.html | 认真就输

该日志由 huangtingzhong 于2013年06月30日发表在 BASIC 分类下,
原创文章转载请注明: 启用与禁用表的约束是否会导致存储过程无效 | 认真就输

报歉!评论已关闭.