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,
on the Oracle DB SQL Tuning Guide:
"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:
- quit on sqlplus usually commits (and while we are at it - quit and exit are synonyms)
- don't want sqlplus to commit upon exit? use the "exitcommit" setting
