哪种操作需要哪种类型的library cache lock / library cache pin?

msdnchina

于 2016-10-09 10:12:56 发布

阅读量668

点赞数

文章标签： library cache pin library cache lock

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

本文详细阐述了Oracle数据库中不同类型的DDL操作、查询及DML操作所需的锁和Pin模式。例如，所有DDL操作需要独占锁和Pin；使用对象的操作（如执行视图或过程）需要空锁和共享Pin；而查询和DML游标则需要空锁和共享Pin。这些锁和Pin在SQL语句执行期间保持，并在其结束时释放。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

如下摘自:
How to Analyze Library Cache Timeout with Associated: ORA-04021 'timeout occurred while waiting to lock object %s%s%s%s%s.' Errors (文档 ID 1486712.1)

Which lock/pin mode is required for which type of action?

    All DDL operations (For example, when a package is recompiled, when a grant is given on an object, a truncate on a table, etc.)
require a exclusive lock and pin on the object that needs to be processed.
The DDL cursors themselves require a null lock (localisation job in library cache) and an exclusive pin when executing.
    All operations that use an objects (e.g. when a view is used, when a procedure is executed, etc.) require a null lock and shared pin on those objects.
This is also applicable for all dependent objects (e.g. view based on another view, package using another package or view).
    Query and DML cursors requires a null lock and shared pin when executing.

The locks/pins are held for the duration of the SQL statement, and released at the end of it.