Two Implicit Commits Triggered By DDL?

When executing a Data Definition Language (DDL) statement, Oracle will issue two implicit commits.

1. Prior to any syntactically correct DDL statement, even if the statement fails to execute properly.
2. After the successful execution of any DDL statement.

When one executes a DML statment. Oracle will basically do this

BEGIN
--Oracle does the first impicit commit
COMMIT; 
--execute the ddl
create table..
--Oracle does a second implicit commit
COMMIT;

exception 
when others then
ROLLBACK;
raise;
end;

If you are not aware of this, it might lead to some unexpected outcomes. However, there is one exception to these rules, which I will show later on

#1 Create one table. Insert some data. Create another table. Issue a rollback.

CREATE TABLE t1 (col1 NUMBER);
Table t1 created.

INSERT INTO t1 (col1) values(1);
1 row inserted.

CREATE TABLE t2 (col1 NUMBER);
Table T2 created.

ROLLBACK;
Rollback complete.

select * from t1;

COL1 
---- 
   1

 

Even though I did a rollback, my DML statement (INSERT) was implicitly committed by the second DDL statement.

#2 Create one table. Insert some data. Create a view on that table.

CREATE TABLE t1 (col1 NUMBER);
Table t1 created.

INSERT INTO t1 (col1) values(1);
1 row inserted.

CREATE VIEW t1_view AS SELECT * FROM t1;
View T1_VIEW created.

ROLLBACK;
Rollback complete.

SELECT * FROM t1;

COL1 
---- 
   1

Same as the first example, the data is not rolled back.

#3 What about TRUNCATE?

#1
CREATE TABLE t1 (col1 NUMBER);
Table t1 created.

INSERT INTO t1 (col1) values(1);
1 row inserted.

TRUNCATE TABLE t1;
Table T1 truncated.

ROLLBACK;
Rollback complete.

SELECT * FROM t1;

0 rows selected

#4 TRUNCATE is also a DDL command, with an implicit commit. So there is no possibility to rollback that data.

#5 What about adding a column comment?

CREATE TABLE t1 (col1 NUMBER);
Table t1 created.

INSERT INTO t1 (col1) values(1);
1 row inserted.

COMMENT ON COLUMN t1.col1 IS 'This is a comment';
Comment created.

ROLLBACK;
Rollback complete.

SELECT * FROM t1;

COL1 
---- 
   1

So even a COMMENT is a DDL command.

Anything that needs to update the data dictonary, have implicits commits!

#6 But I stated before that there is an exception to this rule. In Oracle 18c, private temporary tables were introduced

CREATE TABLE t1 (col1 NUMBER);
Table t1 created.

INSERT INTO t1 (col1) values(1);
1 row inserted.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_t1 (col1 number) ON COMMIT DROP DEFINITION;

ROLLBACK;
Rollback complete.

SELECT * FROM t1;

0 rows selected.

Here we can see that there is no implicit commit. This is the only exception to the rule that all valid DDL statements with successful execution perform implicit commits.

# 7 What about if the syntax is correct, but for example the column I want to make a comment on does not exist?

CREATE TABLE t1 (col1 NUMBER);
Table t1 created.

INSERT INTO t1 (col1) values(1);
1 row inserted.

COMMENT ON COLUMN t1.col2 IS 'This is a comment';
ORA-00904: "COL2": invalid identifier

ROLLBACK;
Rollback complete.

SELECT * FROM t1;

COL1 
---- 
   1 

The syntax is valid, so we will get an implicit commit.

#8 If the syntax is not correct?

CREATE TABLE t1 (col1 NUMBER);
Table t1 created.

INSERT INTO t1 (col1) values(1);
1 row inserted.

COMMENT ON COLUMN t1.col2 IS ();
ORA-01780: string literal required

ROLLBACK;
Rollback complete.

SELECT * FROM t1;

0 rows selected.

Here we can see that there was no implicit commit after the insert, and the statement was rolled back.

As with my previous post about datatype conversion, don’t just take my word for it. Test it yourself 🙂

Over&Out