When an in-progress DDL operation executes or references a schema object (note), the Data dcictionary (DDL) lock is used to secure the definition of the schema object, and only a single schema object is locked during the execution of the DDL operation, and the database never locks the entire dictionary.
The Oracle database automatically requests a DDL lock, and the user cannot display the request DDL lock. For example, if a user creates a stored procedure, the database automatically requests DDL locks for the objects referenced in the stored procedure, which prevents the object from being altered or deleted until the stored procedure is compiled.
Row It DDL lock
An exclusive DDL lock prevents the other session from acquiring DDL or DML locks. Most DDL operations, except those described in the shared DDL lock, have DDL locks for resource requirements, for example: DROP table is not allowed to be executed when ALTER TABLE is adding a column to the table, and vice versa.
The exclusive DDL lock continues until the DDL operation completes and is automatically committed, and during this time, if other operations require the schema object, wait until the old DDL lock is released.
Shared DDL Locks
A shared DDL lock prevents DDL operations that can break conflicting structures, but allows similar DDL operations to execute in parallel.
For example, when a CREATE procedure is executed, the transaction requires shared DDL locks for all reference tables, other transactions can create stored procedures that reference the same table in parallel and request shared DDL locks on the same table, but no transaction can request an exclusive DDL lock on any referenced table.
A shared DDL lock continues until the DDL operation completes and is automatically committed, so a transaction that acquires a shared DDL lock is guaranteed to remain unchanged during transaction execution.
Interruptible Parse Lock
Each schema object referenced in the SQL statement and the PL-SQL block uses a parse lock, because if a reference object is changed or deleted, the corresponding shared SQL domain (note) will be invalidated. A parse lock is called an interruptible parse lock (breakable parse Lock) because it does not block any DDL operations and can be interrupted in order to allow conflicting DDL operations.
A parse lock is required when parsing an SQL statement, and if the shared SQL domain of the statement is kept in the shared pool, the lock is held.
Note:
Schema is a collection of names of database objects;
The schema object is the logical structure of the data stored in a schema;
A shared SQL domain (GKFX SQL area) is a parse tree and execution plan that is saved for an SQL statement in a shared pool, and only one shared SQL domain exists for a statement.
Oracle Lock 3:DDL Lock
