Oracle HANGS when I try to compile trigger.

Hi, guys
we have a Oracle 7.3
I create triggers for it, they were compiling without any problems before I restrat Oracle. Now when I try to compile trigger or package Oracle session hangs. In the lock table I can see that my user session holds Exclusive Transaction lock (SID=9), object_id=372 is :
SELECT NAME FROM sys.obj$ where obj#=372
CLU

Code:

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 0A533B48 0A533BF8 9 TX 327798 372 6 0 0 0 08C09694 08C096A4 3 RT 1 0 6 0 0 0 08C096C8 08C096D8 2 MR 6 0 4 0 0 0 08C096FC 08C0970C 2 MR 5 0 4 0 0 0 08C09730 08C09740 2 MR 4 0 4 0 0 0 08C09764 08C09774 2 MR 3 0 4 0 0 0 08C09798 08C097A8 2 MR 2 0 4 0 0 0 08C097CC 08C097DC 2 MR 1 0 4 0 0 0

When I try to kill session it does not affect on hanging session, and field status in the v$session has value 'killed'.
In the same time I can create/drop table. make a query.

I will appreciate any advices.

Oracle 7.3? What's that?

Hi,
Your session could be hanging if its waiting for some resource,currently used by other sessions or process.Try quering v$system_event,v$session_event and finally v$session_wait to get an idea of the events waiting for the resource.Probabaly ur session might be experienceing "buffer busy waits",if ur triggering is doing some DML activites or having some I/O related problems.

regards
anandkl

try to use orakill utility.

The trigger you are trying to compile, references other objects (packages,procedures,functions,triggers with PL code)?

Angel

Yes, trigger reference to a package, which I can not compile as well by the same reason, nobody use that database except of me and nobody use objects related to a trigger or package, I do not make any changes with package when i compile trigger, I do not make any simultaneous compilations of the trigger or package.

Thank you very much.

Quote:

Originally posted by aarroyob
The trigger you are trying to compile, references other objects (packages,procedures,functions,triggers with PL code)?
Angel

The package reference the trigger too? If you have circular reference you will drop one of them (if you can) or remove one or both from fet$.

Hope that helps

Angel

Hi, anandkl, I have checked that views:

Code:

select * from v$session_event where sid=9; SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT ---------- ------------------------------ ----------- -------------- ----------- ------------ 9 db file sequential read 41 0 0 0 9 SQL*Net message to client 65 0 0 0 9 SQL*Net message to dblink 79 0 0 0 9 SQL*Net message from client 65 0 0 0 9 SQL*Net message from dblink 79 0 0 0 9 SQL*Net more data from dblink 2 0 0 0 ====================================== select * from v$system_event; EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT ------------------------------ ----------- -------------- ----------- ------------ pmon timer 17193 17193 0 0 process startup 6 0 0 0 rdbms ipc reply 20 0 0 0 rdbms ipc message 51641 51608 0 0 control file sequential read 122 0 0 0 control file parallel write 49 0 0 0 log file sequential read 10 0 0 0 log file single write 7 0 0 0 log file parallel write 46 0 0 0 log file sync 2 0 0 0 db file sequential read 490 0 0 0 db file scattered read 104 0 0 0 db file single write 18 0 0 0 db file parallel write 9 0 0 0 instance state change 2 0 0 0 smon timer 174 171 0 0 SQL*Net message to client 871 0 0 0 SQL*Net message to dblink 79 0 0 0 SQL*Net more data to client 50 0 0 0 SQL*Net message from client 870 0 0 0 SQL*Net more data from client 202 0 0 0 SQL*Net message from dblink 79 0 0 0 SQL*Net more data from dblink 2 0 0 0 SQL*Net break/reset to client 14 0 0 0 ================================== select * from v$session_wait; SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE ---------- ---------- ------------------------------ ---------- ---------- -------- ---------- ---------- -------- ---------- ---------- -------- ---------- --------------- ------------------- 1 17195 pmon timer duration 300 0000012C 0 00 0 00 0 0 WAITING 2 17284 rdbms ipc message timeout 300 0000012C 0 00 0 00 0 0 WAITING 3 17344 rdbms ipc message timeout 300 0000012C 0 00 0 00 0 0 WAITING 6 33 rdbms ipc message timeout 180000 0002BF20 0 00 0 00 0 0 WAITING 4 17195 rdbms ipc message timeout 300 0000012C 0 00 0 00 0 0 WAITING 5 308 smon timer sleep time 300 0000012C failed 0 00 0 00 0 0 WAITING 7 1050 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 -2 0 WAITED UNKNOWN TIME 8 50 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 0 0 WAITING 9 332 SQL*Net more data from dblink driver id 1129532494 43534C4E #bytes 195 000000C3 0 00 0 0 WAITING 9 rows selected.

Quote:

Originally posted by anandkl
Hi,
Your session could be hanging if its waiting for some resource,currently used by other sessions or process.Try quering v$system_event,v$session_event and finally v$session_wait to get an idea of the events waiting for the resource.Probabaly ur session might be experienceing "buffer busy waits",if ur triggering is doing some DML activites or having some I/O related problems.
regards
anandkl