Help - Search - Members - Calendar
Full Version: How to unlock library objects
Oracle DBA Forums > Oracle > Oracle Forum
Mohan85
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
dave
so you are saying you have library cache locks on some objects? - find the blocking sessions and kill them
Mohan85
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.

QUOTE (dave @ Nov 21 2007, 08:51 AM) *
so you are saying you have library cache locks on some objects? - find the blocking sessions and kill them
burleson
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
Oleg Khaykin
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?
dave
because the package is dependant on that object, that object is being updated

isnt the protection obvious?
Oleg Khaykin
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.
SteveC
QUOTE (Oleg Khaykin @ Jul 23 2008, 11:47 AM) *
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?

QUOTE
DML Locks

The purpose of a DML lock (data lock) is to guarantee the integrity of data being accessed concurrently by multiple users. DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations. DML statements automatically acquire both table-level locks and row-level locks.
Oleg Khaykin
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?
burleson
>> 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:
Oleg Khaykin
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!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.