Search this blog

Wednesday, January 27, 2016

a failed ddl will commit on Oracle DB

Let us view a real-life recorded scenario

SQL> create table test2 (n1 number);
Table created.

SQL> insert into test2 values (1);
1 row created.

SQL> c/1/2
  1* insert into test2 values (2)

SQL> /
1 row created.

SQL> commit;
Commit complete.

SQL> insert into test2 values (3);
1 row created.

SQL> create view viewtest2 as select * from test2;
create view viewtest2 as select * from test2
            *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> rollback;
Rollback complete.

SQL> select * from test2;
        N1
----------
         1
         2
         3

SQL>


Why is that
lets take a look at the execution order of a DDL in Oracle,
"Typically, the database would run dozens of recursive statements to execute the preceding statement. The recursive SQL would perform actions such as the following:
  •  Issue a COMMIT before executing the CREATE TABLE statement
  •  Verify that user privileges are sufficient to create the table
  •  Determine which tablespace the table should reside in
  •  Ensure that the tablespace quota has not been exceeded
  •  Ensure that no object in the schema has the same name
  •  Insert rows that define the table into the data dictionary
  •  Issue a COMMIT if the DDL statement succeeded or a ROLLBACK if it did not" 
one could say that this is a necessity, 
but one could also claim that the first commit could have been executed at a later stage 
(after all the checks and just before writing into the data dictionary); 

if you wish to differentiate the commit from the regular flow, one way to workaround this, suggested by tom kyte, is to use an autonomous transaction. Another way is to use a savepoint. 



Related info: