Why can Oracle's DDL statements not be rolled back

On the itpub, I saw someone raise the question. In SQL Server or some other database, DDL statements can be rolled back, so why can't oracle rollback DDL statements?

To illustrate this issue, first you need to explain what a DDL statement is. A DDL statement is a data definition statement that includes the creation, modification, and deletion of various data objects, as well as authorization operations.

In Oracle, DDL statements are converted to DML statements that modify the data dictionary table. A simple DDL statement that modifies a table can cause Oracle to do a lot of querying and modifying operations in the background through recursive SQL statements.

If you are interested, you can check what the Oracle background actually does by sql_trace the DDL statements.

In Oracle, Oracle emits a COMMIT statement before executing the DDL, then performs a DDL operation, and finally sends a commit.

As mentioned earlier, for Oracle, the DDL is actually a series of modifications to the data dictionary table, the DML operation of the data dictionary table, so that Oracle is theoretically fully capable of implementing the rollback of DDL statements, so why is Oracle designed to work the way it does now. Oracle is known for its flexibility and strong customization, but Oracle does not give users any possibility of rolling back the DDL, and there is a good reason to be there.

First of all, why Oracle to execute a commit before and after the DDL statement, in fact, is very simple, Oracle is to the user's read and write operations and data dictionary changes in isolation, user data should not be read and written data dictionary operation in the same transaction.

To illustrate why Oracle does not rollback DDL statements, let's assume that Oracle can roll back DDL statements to see what impact this will have on Oracle databases.

This column more highlights: http://www.bianceng.cn/database/Oracle/

From now on, assume that the DDL is not automatically committed, but a part of the transaction.

The DDL is then satisfied with the read commit isolation mechanism, meaning that the DDL statements executed by the user are not visible to other users until they are committed. For example, a user executes the statement of Create TABLE T and then performs some DML on T. At this point, other sessions are unable to see the T-table.

In this case, there is a table T, which contains two columns, an ID column, and a created column.

A session executed ALTER TABLE T MODIFY CREATED DEFAULT sysdate not NULL, and then made some insertions to the T table, but did not commit.

The B session attempts to insert the T-table, and if the DDL statement is not part of the transaction, there is no conflict between the insert of B and the insertion of a session, but the situation is different now because a performs a modified T-table, adds a default value to the created column, and sets to not NULL. And this modification B session is currently invisible because a does not commit the modification. At this point, if the insert of the B session does not provide a value for the created column, the insert operation is locked. In the case of B, the created column in the table structure is still nullable, allowing the insertion of created columns to be empty, but since a has set the created column of T to be non-null and contains the default value, the insertion of B must be locked, otherwise if A and B are all committed, A session finds that a created is still empty in the T table even if the DDL statement is executed. In order for the DDL to be rolled back and for multiple-version read consistency, Oracle must lock the modified table after the DDL occurs to avoid inconsistencies in the access of other sessions. This can lead to a lock escalation in oracle and a severe impact on Oracle concurrency and greatly increase the likelihood of deadlock generation.

Maybe someone is wondering why SQL Server or some other database can implement a rollback of a DDL statement. In fact, it is mentioned earlier that Oracle is also capable of implementing DDL rollback, but this can greatly affect Oracle concurrency. You know, Oracle's lock mechanism and multiple versioning consistency make Oracle concurrency the first in all database products. It is clear that Oracle loses the most commendable concurrency in order to achieve a rollback of the DDL. Other databases can be implemented because the locking mechanism of these databases is inherently flawed, such as a large number of locks that occupy the system's resources, read and write operations are blocked, and row-level locks may be automatically upgraded to table-level locks. Because these problems already exist, implementing a rollback of the DDL does not significantly exacerbate concurrency, because even if the DDL does not escalate row locks to table locks, other factors may cause this to happen.