DDL Statement Processing in an Oracle Database

YouTube video

Introduction

In this article, we will delve into the process of how DDL (Data Definition Language) statements are processed in an Oracle Database. DDL statements are used by users to create, alter, or drop objects such as tables, indexes, procedures, and functions within the database. We will explore how these statements impact the metadata and the system tablespace, and the implicit commit that occurs during their execution.

Understanding DDL Statement Processing

When a user issues a DDL statement, such as creating a table or index, it primarily affects the metadata of the database. For example, when creating a table, the user specifies the name of the table, along with a list of columns, and their respective data types. This information is used to update the dictionary cache, which acts as a reflection of the database’s metadata.

Internally, a DDL statement in Oracle is treated as a DML (Data Manipulation Language) operation on the internal data stored in the system tablespace. This means that when you execute a DDL statement, you are essentially modifying the rows in the system tablespace, which houses the metadata.

Implicit Commit and System Data

To ensure data consistency and integrity, Oracle automatically performs an implicit commit after executing a DDL statement. This means that any changes made to the system data during the execution of the DDL statement are permanently stored.

It’s important to note that a DDL statement does not directly alter user data. If a table is dropped, for example, the data within the table is lost. However, Oracle does not go through each row individually to delete the data, as it would with a DML statement. Instead, the entire object, such as the table, is dropped, resulting in the deletion of its data.

DDL Statement Processing Steps

Now that we understand the basics of DDL statement processing, let’s outline the step-by-step process:

  1. User initiates a DDL statement: The user issues a DDL statement, specifying the desired action, such as creating, altering, or dropping an object.

  2. Parsing the DDL statement: The Oracle Database parses the DDL statement to ensure its syntax is correct and that the requested action is valid.

  3. Updating the dictionary cache: If the syntax and action are valid, the Oracle Database updates the dictionary cache. This involves creating a new entry in the cache for the object being created or altered, or removing an existing entry for a dropped object.

  4. Modifying the system tablespace: After updating the dictionary cache, the Oracle Database performs the necessary modifications to the system tablespace. This could involve adding or modifying rows that reflect the changes requested in the DDL statement.

  5. Implicit commit: To make the changes permanent, an implicit commit is executed. This commits the changes made to the system data, ensuring data consistency.

Conclusion

In conclusion, DDL statement processing in an Oracle Database involves updating the metadata stored in the dictionary cache and modifying the system tablespace. DDL statements impact the metadata and do not directly alter user data. An implicit commit is executed automatically after a DDL statement to make the changes permanent. Understanding this process is crucial when working with DDL statements in Oracle.

Remember, the next time you create, alter, or drop an object in your Oracle Database, you’ll have a better understanding of how the DDL statement is processed behind the scenes.