>> 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
See Code depot for complete script
b.name in ('latch free','library cache load lock',
'library cache lock','library cache pin')