Create Schema - Bundling DDL

Learnt something newly today in Oracle, its about bundling DDL's in a single Transaction.

Create Schema Statement available in Oracle allows you to combine multiple create tables, create views and Grants in a single Transaction. If all the statments executes successfully then Oracle commits the Transaction or else it will be rolled back entirely.

test@9IR2> create schema authorization test
  2     create table t(x number,y varchar2(10),z date)
  3     create view v as select * from t
  4     grant select on t to public
  5     grant select on v to public;

Schema created.

Elapsed: 00:00:00.01

test@9IR2>
test@9IR2> desc t;
Name        Null?    Type
---------- -------- ---------------
X                    NUMBER
Y                    VARCHAR2(10)
Z                    DATE

test@9IR2>
test@9IR2> drop table t ;

Table dropped.

Elapsed: 00:00:00.01
test@9IR2> drop view v;

View dropped.

Elapsed: 00:00:00.01

test@9IR2>
test@9IR2>

Now the Transaction suceeded entirely and the Table and View will become VALID schema objects. If the Create Schema fails it will never create Table and View, it will rollback the entire Transaction.

test@9IR2>
test@9IR2> create schema authorization test
  2     create table t(x number,y varchar2(10),z date)
  3     create view v as select * from test_foo
  4     grant select on t to public
  5     grant select on v to public;

create schema authorization test
*
ERROR at line 1:
ORA-02427: create view failed

Elapsed: 00:00:00.01

test@9IR2>
test@9IR2> desc t;

ERROR:
ORA-04043: object t does not exist

test@9IR2> desc v;
ERROR:
ORA-04043: object v does not exist

test@9IR2>
test@9IR2>