Oracle HANGS when I try to compile trigger.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Oracle HANGS when I try to compile trigger.

  1. #1
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

    Angry

    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.
    Best wishes!
    Dmitri

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Oracle 7.3? What's that?

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  4. #4
    Join Date
    Dec 2001
    Posts
    221
    try to use orakill utility.
    Santosh Jadhav
    8i OCP DBA

  5. #5
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    The trigger you are trying to compile, references other objects (packages,procedures,functions,triggers with PL code)?

    Angel

  6. #6
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    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.

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

  7. #7
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    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

  8. #8
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    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.
    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
    Best wishes!
    Dmitri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width