In this article I’ll be talking about TRANSACTIONAL DDL in Oracle. Hopefully, this will be helpful for your awareness.
For those who are interested in Oracle usually have certain ideas as to how transaction management is performed in Oracle. (For those who are unaware of this can take a look at the following links so as to have a rough idea. Transactions-1 Transactions-2 Transactions-3). Based on this, it’s obvious that transaction logic of DDL and DML operations is essentially different. In this writing, we’ll browse how DDL operations, which are autocommit, can be processed in a transactional way.
We possess the information that an implicit commit is run before and after DDL operations. It can be said that with this performance, a transaction is launched and ended for each DDL. However, Oracle can render this performance transactional for certain DDL operations, though not all of them. In other words, for successive DDL operations, a new transaction isn’t launched and ended. A transaction is launched for the whole of the related DDL, or all of them work successfully, or all DDL operations are undone when one receives an error.
So, which DDL operations can I run in a single transaction?
- CREATE TABLE
- CREATE VIEW
After giving the basics, I’ll exemplify this operation.
We use the phrase CREATE SCHEMA AUTHORIZATION for this operation. However, to use this phrase, first we need to follow some rules. These rules are:
- After the phrase CREATE SCHEMA AUTHORIZATION, we need to enter a schema user. This user, though, must be the same schema user that we’ll use to connect to database, run the related DLLs, which will consequently create objects.
For example, if I want to create a table under HR user, I need to connect to DB with HR user, write CREATE SCHEMA AUTHORIZATION HR and start this operation. Here, we need to remember this detail: We don’t create a new schema with CREATE SCHEMA command. With this phrase, we tell the system that we won’t use more than one transaction for DDLs.
Now let’s exemplify the topic at hand.
CREATE SCHEMA AUTHORIZATION hr CREATE TABLE authdetail ( id NUMBER PRIMARY KEY, depname VARCHAR2 (10) ) GRANT ALL ON authdetail TO SCOTT CREATE TABLE authmaster ( id NUMBER PRIMARY KEY, name VARCHAR2 (10), surname VARCHAR2 (10), departmentid NUMBER, CONSTRAINT fk_x FOREIGN KEY (departmentid) REFERENCES authdetail (XYXXXX) );
So, I intentionally made a mistake in the constraint that we form while creating authmaster table, and caused the whole ddl statement to receive an error. Due to this error, while authdetail was successfully created, the table was not. We can observe that GRANT command, which also ran successfully, was undone.
SELECT * FROM user_tables WHERE table_name = 'AUTHDETAIL'; -- no rows selected. SELECT * FROM table_privileges WHERE owner = 'HR' AND TABLE_NAME = 'AUTHDETAIL'; -- no rows selected.
As can be understood from the example, we can make all DDL statements transactional. With this feature, we can avoid the separation of transaction in code blocks, in which we use DDL and DML statements jointly.