Weird library cache lock issue

[Brian Bientz]

We recently had a situation were an unknown library cache lock wreaked havoc on our system. As best we could tell, a library cache lock was blocking all subsequent logins to the database. Additionally, existing connections were hung in a wait state. After using a couple queries we found on Oracle's metaLink we were able to isolate the session that was holding the locks. After killing the session, everything cleared up. Unfortunately, we weren't able to determine the root cause.

Has anyone experienced a similar situation?

[Patrick Wallek · Accepted solution]

Check out doc id 1200542571 in the ITRC TKB.

It may be relevant to your situation.

[A. Clay Stephenson]

I'm working from memory here but this is something that shouldn't happen open a production machine because nobody's changing views, packages, or procedures (right); you did all that on the test/development box where such locks are not uncommon.

It typically occurs when someone is recompiling a procedure that is in use by running applications.

To avoid these events on production boxes, you must be aware of what triggers them and then take care not to compile or alter objects while they might be in use.

[Anup]

Yes, and now I think this should be common to all unix based systems regardless of the backend.

In my problem we are using Compaq (TMF) back-end and my request for add or update to the backend server, if suspended, still holdons to the resources. (this makes sense because the suspended process can always be brought to the foreground).

The only solution we could figure was to check there are suspended operations every x seconds (or mins) in an automated script and kill those! (not the best solution i believe). I would also like to know if someone has a better thing to suggest..