ORA-4021 的发生原因
5,097 total views, 1 views today
An Oracle instance contains a cache that holds the definitions of each different type of objects e.g. cursors, indexes, tables, views, procedures, etc. This cache is called the library cache and contains a ‘library’ of object definitions. While these objects are in use, these definitions cannot be changed since if they were then all sorts of problems would ensue. They are protected by a series of locks and pins called library cache locks and pins.
Oracle 实例包含一个缓存区，用于存放每个不同类型对象的定义，如cursors、indexes、tables、views、procedures等等。这个缓存区也就是library cache。当这些对象使用时，这些定义不能被改变，否则会出现各种各样的问题。这些对象由一系列locks和pins保护，被称为library cache locks 和 library cache pins。
A session that needs to use an object will first attempt to acquire a library lock in a certain mode on the object (null, shared or exclusive). If it gets the library cache lock, this will prevent other sessions from modifying it (e.g. an exclusive lock will be taken when recompiling a package or view). If the lock is not available, it will wait until it can get it (i.e. when another session has stopped using it). Locking an object is the first step and is essentially the task of locating the object in the library cache and then lock it in a certain mode. If the session wants to modify or examine the object, it must also acquire a pin in a certain mode (again null, shared or exclusive) after the lock has been taken.
一个会话使用一个对象，首先尝试获取一个对象的特定模式下的library cache lock(null, shared or exclusive)。如果获取到了library cache lock，会阻止其他会话修改该对象（如，当重新编译一个包或者视图时，必须首先获取到排它锁）。如果获取不到library cache lock，一直等待直到获取到位置（也就是，要等待其他会话停止使用该lock）。锁定对象是第一步，实际上是将对象放置到library cache中，然后将其锁定的特定模式下。如果会话希望修改或者检查对象，在获取到对象的library cache lock之后，必须获取一个特定模式的pin（again null, shared or exclusive）。
Each SQL statement that wants to use/modify objects that are already locked or pinned (and whose lock/pin mode is incompatible with the requested mode), will wait on events like ‘library cache pin’ or ‘library cache lock’ until it is freed and it can get the lock. If it waits longer than a pre-defined threshold, a timeout occurs. This is done to free up resources in the waiting session that might be blocking other sessions rather than having a series of sessions that cannot move.
对象已经被locked或者pinned，每条SQL语句想要使用/修改该对象，就会产生等待事： ‘library cache pin’ 或者 ‘library cache lock’，直到lock和pin被释放，然后获取到lock和pin，等待事件才会消失。如果等待时间超过了预定义的阈值，则发生超时。超时是为了清理释放那些占有资源，并阻塞其他会话的会话。
If it times out then a ORA-4021 error is raised:
如果超时，则会产生ORA-4021错误
ORA-04021 timeout occurred while waiting to lock object %s%s%s%s%s.
Cause: While trying to lock a library object, a time-out occurred.
Action: Retry the operation later.
The timeout normally occurs after 5 minutes.
超时通常发生在5分钟之后。
It is also possible for a library cache deadlock to occur. This is a similar situation to the above except that in this case the holder is also waiting for the waiter. In most cases this is automatically detected and a ORA-4020 is returned (in the same way as an ORA-00060 error is raised fro an application deadlock):
也可能发生library cache deadlock 。原因类似于上面的情况。大多数情况，自动侦测到并返回ORA-4020。
ORA-04020 deadlock detected while trying to lock object %s%s%s%s%s
Cause: While trying to lock a library object, a deadlock is detected.
Action: Retry the operation later.
对排查library 锁定问题有帮助的视图
DBA_KGLLOCK : one row for each lock or pin of the instance
-KGLLKUSE session address
-KGLLKHDL Pin/Lock handle
-KGLLKMOD/KGLLKREQ Holding/requested mode
0 no lock/pin held
1 null mode
2 share mode
3 exclusive mode
-KGLLKTYPE Pin/Lock
(created via the $ORACLE_HOME/rdbms/admin/catblock.sql)
V$ACCESS : one row for each object locked by any user
-SID session sid
-OWNER username
-OBJECT object name
-TYPE object type
V$DB_OBJECT_CACHE : one row for each object in the library cache
-OWNER object owner
-NAME object name or cursor text
-TYPE object type
-LOCKS number of locks on this object
-PINS number of pins on this object
DBA_DDL_LOCKS : one row for each object that is locked (exception made of the cursors)
-SESSION_ID
-OWNER
-NAME
-TYPE
-MODE_HELD
-MODE_REQUESTED
V$SESSION_WAIT : each session waiting on a library cache pin or lock is blocked by some other session
-p1 = object address
-p2 = lock/pin address