compile trigger "reuse settings" making difference? ORA-04020

hi, all,

   We have a case, when adding column to database table, caused sessions hang. Our oracle is 9205 running on solaris.

1. The same code in test 9205 does not report error, but in production database with several thousand connections, it caused high session when most other session waiting on library cache pin/library cache lock. Hanganalyze does not report a valid blocker.
2. The same code give out orror in 817 instance. but does not error in test 9205 version.

   What I noticed from trace file, is , the 9205 version, compiled the trigger with reuse setting, but 817 instance doesnot. Is this making the difference?Anyone has some experience with that? All the SQL are executed by oracle internally. Recursive SQL.

you can test with the following test code:

set line 200 echo on
col object_name format a40

create table test as select * from dba_tables; alter table test add last_modified date;

 create or replace trigger trig_test before update on test  for each row
 begin
 :new.last_analyzed:=sysdate;
 end;
/

  update test set owner='A' WHERE ROWNUM=1;

  ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';   SELECT OWNER,LAST_ANALYZED FROM test WHERE OWNER='A';

  ALTER TABLE test ADD TESTCOL DATE;

  SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS;    select object_name,status from user_objects where object_name='TRIG_TEST' or object_name='TRIG2';

   set timing on
   ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 8';  update test set owner='A-TRITEST' WHERE OWNER='A';    ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT off';

  SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS;    select object_name,status from user_objects where object_name='TRIG_TEST' or object_name='TRIG2';

--

Regards
Zhu Chao
www.cnoug.org

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jan 16 2006 - 05:59:56 CST