TRANSACTIONAL DDL BASIS WITH CREATE SCHEMA PHRASE

Hi everyone,

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
  • GRANT

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.

REFRENCES

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6014.htm

https://blogs.oracle.com/sql/entry/creating_multiple_tables_in_a

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Root and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s