Dear members,


I Got a broblem with my library cache objects. Some of my library objects were locked which results in DML locks and also it hang the database.

Can anyone assist me to unlock those library objects

Please... It's very very urgent. Plzz help me out asap.



Thanks in advance

Anand

so you are saying you have library cache locks on some objects? - find the blocking sessions and kill them

Ya. for the first time i did like that. But i'm getting that problem regularly. What to do now?? Please send me quick reply.

Hi Anand,

>> Some of my library objects were locked which results in DML locks and also it hang the database.

The library cache pin Oracle metric takes place if the process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. The library cache pin wait usually happens when you are compiling or parsing a PL/SQL object or a view.

The Oracle Documentation notes:

"The library cache lock Oracle metric event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:

- one client can prevent other clients from accessing the same object
- the client can maintain a dependency for a long time (e.g., no other client can change the object)
- This lock is also obtained to locate an object in the library cache."

>> Can anyone assist me to unlock those library objects

See here, and run the script to examine the nature of the waits:

http://www.oracle-training.cc/teas_elite_troub_14.htm

select
name, waits
from
sys.v$system_event a,
sys.v$event_name b
where
See Code depot for complete script
b.name in ('latch free','library cache load lock',
'library cache lock','library cache pin')
group by
b.name

Why does a PL/SQL package body need to be locked by a session executing an UPDATE on one of the tables used in the package body? Until the UPDATE is complete, another session can not re-compile the package body; it waits on "library cache pin" and finally times-out. Why? What is Oracle trying to protect this way?

because the package is dependant on that object, that object is being updated

isnt the protection obvious?

Not obvious at all. The object (table) is not being altered. Only the data in the table is being updated. To me, it is safe to re-compile the package while the data is being updated. Obviously, Oracle developers think conversely.

Yes, not only conversely, but correctly.

What if the package code drops and creates the table, or not even the table you are updating, but a dependent object related to that table?

Steve, in PL/SQL, you can drop/create tables and other objects only by using dynamic SQL (EXECUTE IMMEDIATE or DBMS_SQL). Neither of these two methods would create a dependency and you would be able to re-compile the package. Also, I am not questioning the necessity of the DML/DDL locks on the table in this situation. Of course, the updated rows and the structure of the whole table need to be protected until the UPDATE is commited. I also agree that ALTERING the table while the dependent package is being compiled or executed should be prevented because it can potentially invalidate the package. Note: starting with 10g, Oracle allows DROPPING a table while the dependent package is being executed; Oracle uses the RECYCLE BIN copy in this case.

My question remains: why do we need to lock the dependent package body while updating the data in the table? Isn't it an over-cautiousness?

>> I am not questioning the necessity of the DML/DDL locks on the table in this situation.

I donno, maybe we should. When designing databases, too many locks are better than too few, and it;s possible that a more selective method could be written.

If the issue can be properly described, it can be submitted to Oracle as an enhancements request:

That's true that I had to wait for 2 hours yesterday before I could recompile the package. However, I hit this problem for the 1st time in 14 years of working with Oracle DB. The loss of 2 hours in 14 years does not seem to be significant enough to bother Oracle. Besides, I could do my UPDATE a smarter way, so it would not take 2 hours. When writing to this forum, my main purpose was to make sure that I am not missing something important in my understanding of how Oracle works with the library cache. Thank you all for your responses!