timeout occurred while waiting to lock object

[khussain]

We have a 4-node RACed database (9.2.0.2.0). Last night many of our nightly batch jobs aborted with this error:

ORA-04021: timeout occurred while waiting to lock object SYS.DBMS_SYS_SQL

They were all trying to do a truncate table, via a package that we have created to control truncate on specific schemas (this pkg has been working fine for months-no problems). This error was received on all the instances. I did take a look at the global view of v$access, and saw 1 or 2 other processes using the package, but nothing was different from the previous nights; same schedule. I did not see anything in dba_blockers. Also, I was able to use the package to do a truncate, as a test, from a sql*plus session !

Anything else I should have done, looked into ?

We ended up recyclying all the instances, and the problem was gone ! I hate these kind of issues - nothing to look into, no traces, no dump.

I would appreciate any input-thanks.

[chiaowen]

for those standard packages, sometimes it worked by reset the SYS password from the server.

[PAVB]

Next time login as SYS and try...

select  decode(lob.kglobtyp, 
        0, 'NEXT OBJECT ', 
        1, 'INDEX ', 
        2, 'TABLE ', 
        3, 'CLUSTER ', 
        4, 'VIEW ', 
        5, 'SYNONYM ', 
        6, 'SEQUENCE ', 
        7, 'PROCEDURE ', 
        8, 'FUNCTION ', 
        9, 'PACKAGE ', 
        11, 'PACKAGE BODY ', 
        12, 'TRIGGER ', 
        13, 'TYPE ', 
        14, 'TYPE BODY ', 
        19, 'TABLE PARTITION ', 
        20, 'INDEX PARTITION ', 
        21, 'LOB ', 
        22, 'LIBRARY ', 
        23, 'DIRECTORY ', 
        24, 'QUEUE ', 
        28, 'JAVA SOURCE ', 
        29, 'JAVA CLASS ', 
        30, 'JAVA RESOURCE ', 
        32, 'INDEXTYPE ', 
        33, 'OPERATOR ', 
        34, 'TABLE SUBPARTITION ', 
        35, 'INDEX SUBPARTITION ', 
        40, 'LOB PARTITION ', 
        41, 'LOB SUBPARTITION ', 
        42, 'MATERIALIZED VIEW ', 
        43, 'DIMENSION ', 
        44, 'CONTEXT ', 
        46, 'RULE SET ', 
        47, 'RESOURCE PLAN ', 
        48, 'CONSUMER GROUP ', 
        51, 'SUBSCRIPTION ', 
        52, 'LOCATION ', 
        55, 'XML SCHEMA ', 
        56, 'JAVA DATA ', 
        57, 'SECURITY PROFILE ', 
        59, 'RULE ', 
        62, 'EVALUATION CONTEXT ', 
        'UNDEFINED ') object_type, 
        lob.kglnaobj object_name, 
        pn.kglpnmod lock_mode_held, 
        pn.kglpnreq lock_mode_requested, 
        ses.sid, 
        ses.serial#, 
        ses.username 
from    sys.v$session_wait vsw, 
        sys.x$kglob lob, 
        sys.x$kglpn pn, 
        sys.v$session ses 
where   vsw.event = 'library cache lock ' 
and     vsw.p1raw = lob.kglhdadr 
and     lob.kglhdadr = pn.kglpnhdl 
and     pn.kglpnmod != 0 
and     pn.kglpnuse = ses.saddr 
/

[abhaysk]

he would ofcourse have had library cache lock, however i dont see any remedy oracle gives in such cases. It just comes out with timeout after waiting for hundereds of seconds. re-run would work. i guess alter system flush shared pool may work too.

[PAVB]

I would suggest an active strategy to deal with the issue instead of a passive one.

1- You have to determine who's holding the library cache lock, that's why I provided the script.
2- You have to determine the status of the holding session and understand why the lock is held; depending of your findings you have to decide either to wait or to terminate the offending session.

[abhaysk]

Thats right, active strategy is better than passive. However here in this case,
PS the package would/can/should (be able to) be accessed by N number of session simultaneously & all will/can hold a shared lock on that object (momentarily).. if its not than i see oracle is not handling pinned objects properly. terminating a session would/may solve, but point is why any (or rather how any) user session will have exclusive locks on sys owner objects?

-Abhay

[PAVB]

You forgot the "compile" scenario.
If somebody is compiling the package and for some reason it hangs or keeps waiting because somebody is using it a new attempt to use the package would find it locked.
Not saying that's what happened, troubleshoot -perhaps using my script?- is needed for a fair determination of the root cause of the issue.

[abhaysk]

i did consider that, thats why the Q "why any user session will have exclusive locks on sys owned objects".. why anyone would re-complie a sys package unless there is a upgrade, re-run catalog (even this why anyone would like to do?).. recompile of packages will be done only when its invalid, but why would such a package go invalid?..

in a prd environment, such things on SYS schema is hardly done.

- Abhay.