DDL locks in Oracle

Guidance:When we sendDDLCommand is automatically added to the object to be processedDDL lockTo prevent objects from being modified by other users. When the DDL command ends, the DDL lock is released. We cannot explicitly request a DDL lock. Only when the object structure is modified or referenced will the DDL lock be added to the object. For example, when a stored procedure is created or compiled, a ddl lock is added to the referenced object. When creating a view, it also adds DDL locks to the referenced table.

Before executing the DDL command,OracleAn implicit commit command is automatically added, and a specific DDL command is executed. After the DDL command is executed, an implicit commit command is automatically added. In fact, Oracle converts DDL commands to DML operations on data dictionary tables. For example, when we issue a DDL command to create a table, Oracle inserts the table name into the data dictionary table tab $ and inserts the column name and column type in the table into the col $ table. Therefore, you must add implicit commit commands to DDL commands to submit DML operations on data dictionary tables. Even if the DDL command fails, it also issues the submit command.

Let's take a look at the following example to start two sessions, one of which is sess #1 and the other is sess #2. Run the following SQL statement in sess #1:

SQL> insert into t values (1 );
1 row created.

Then, query the data in table T in sess #2:

SQL> select * from t;
No rows selected

Apparently, because sess #1 has not been submitted, the records inserted by sess #1 cannot be retrieved in sess #2. Next, we will execute the following statement in sess #1:

SQL> create table t (c1 number );
Create table t (c1 number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

The command to Create Table T failed because table T already exists. Then we will return to sess #2 to query table T:

SQL> select * from t;
ID
----------
1

Obviously, we didn't issue the commit command in sess #1, but the insert operation in sess #1 has been submitted. This commit is implicitly issued through the create table DDL command, even though the create table command has failed.

DDL locks have the following types:

1. Exclusive DDL Lock)

Most DDL operations add exclusive DDL locks to the objects to be operated, so as to prevent objects from being modified by other users during DDL command execution. After exclusive DDL locks are added to an object, no other DDL locks can be added to the object. If you run DDL commands on a table, other processes cannot modify the data in the table.

2. Shared DDL Lock)

It is used to protect the DDL objects from being updated by other user processes, but allows other processes to add shared DDL locks to the objects. If you run DDL commands on a table, other processes can modify the data in the table at the same time. For example, when the create view command is issued to create a view, the shared DDL command is added to the referenced table (such a table is also called a base table) of the view. That is to say, when creating a view, other users cannot modify the structure of the Base table, but can update the data in the base table.

3. Broken resolution Lock (Breakable Parsed Lock)

The cached SQL cursor in the shared pool or PL/SQL program code will get the resolution lock on the referenced object. If a DDL command is issued to modify the structure of an object, the resolution lock related to the object located in the shared pool is broken, as a result, the SQL cursor or PL/SQL program code that references this object is invalid. The next time you execute the same SQL statement, you need to re-parse it. This is the reload of the so-called SQL statement. A broken resolution lock does not prevent other DDL locks. If a DDL lock that conflicts with the resolution lock occurs, the resolution lock will also be broken.

We mainly use the dba_ddl_locks view to monitor DDL locking. There is no V $ view related to DDL locking. If the dba_ddl_locks view is not found, run the script $ ORACLE_HOME/rdbms/admin/catblock. SQL to create the view. When executing the script, log on to the database as the user sys.

The DDL lock in Oracle introduced above ensures data security in the Oracle database and prevents data loss or tampering in the database.