Oracle Create Schema – multiple DDL statements in a single transaction

I was going to begin with some extended Brexit metaphor to illustrate the chaos and confusion that can ensue when you first encounter Oracle’s CREATE SCHEMA command.
Fortunately, the Dutch Government saved me the trouble :

dutch_brexit_monster

Much as I’d like to believe that the Cookie Monster has finally overcome his Sesame Street type casting, I can’t help noticing that the Brexit Monster never seems to in the same room as this guy…

boris

In Oracle, the term “schema” is used interchangeably with the term “user”. Creating a user in Oracle automatically creates a schema of the same name belonging to that user.
The process is so seamless that it’s almost impossible to tell where one begins and the other ends.
You may therefore be somewhat confused the first time you encounter Oracle’s CREATE SCHEMA command…

Contrary to what you might expect, CREATE SCHEMA does not enable you to create a database user. What it does do is to enable you to run multiple DDL statements inside a single transaction.
Now, if you thought that any DDL statement in Oracle would end with an implicit commit of the current transaction, well that makes two of us (at least).

To demonstrate why this is not necessarily true, let’s say we have a user created like this :

create user brexit_monster identified by lets_go_dutch 
    default tablespace users quota 5M on users
/

grant create session, create table, create view, create procedure to brexit_monster
/

…and a role created like this :

create role erg
/

If we want to create a table in the brexit_monster schema, then a view on that table, followed by a grant to the ERG role we could achieve this with three separate statements in a script…

create table brexit_types( brexit_cid varchar2(30),  classification varchar2(65000))
/

create or replace view promised_land as select brexit_cid from brexit_types where classification = 'HARD'
/

grant select on promised_land to erg
/

…however, if the first statement fails, the next two will also fail leaving something rather messy…

script_errors

If instead, we were to wrap these statements into a single CREATE SCHEMA…

create schema authorization brexit_monster
    create table brexit_types( 
        brexit_cid varchar2(30),  
        classification varchar2(65000))
    create view promised_land as 
        select brexit_cid 
        from brexit_types 
        where classification = 'HARD'
    grant select on promised_land to erg
/   
    

…the error stack is somewhat more manageable

cs_err1

Note however, that the View statement has changed a bit. It’s now a straight CREATE rather than CREATE OR REPLACE.
In fact, if you try to plug any DDL statement into CREATE SCHEMA that is not either a CREATE TABLE, CREATE VIEW or GRANT then you will get :

ORA-02422: missing or invalid schema element

If we issue the correct statement…

create schema authorization brexit_monster
    create table brexit_types( 
        brexit_cid varchar2(30),  
        classification varchar2(5))
    create view promised_land as 
        select brexit_cid 
        from brexit_types 
        where classification = 'HARD'
    grant select on promised_land to erg
/   


Schema created.


…we can see that all of the DDL has been executed…

select object_name, object_type
from user_objects
order by 2
/

OBJECT_NAME		       OBJECT_TYPE
------------------------------ -----------------------
BREXIT_TYPES		       TABLE
PROMISED_LAND		       VIEW

select grantee, privilege
from user_tab_privs
where owner = 'BREXIT_MONSTER'
and table_name = 'PROMISED_LAND'
/

GRANTEE 		       PRIVILEGE
------------------------------ ------------------------------
ERG			       SELECT

At this point you may still be somewhat skeptical about whether all this really happens in a single transaction.

I mean, how do we know that Oracle isn’t just parsing each statement to make sure they’re all valid and then trusting it won’t hit a runtime error ?
One way to find out is, of course, to engineer a runtime error.

You remember when I created the BREXIT_MONSTER user and you thought that I was a bit stingy with the tablespace quota allocation ? Well…

set serverout on
exec dbms_output.put_line('Current transaction = '||dbms_transaction.local_transaction_id(true));

create schema authorization brexit_monster
    create wto_terms(is_problem varchar2(3) default 'NO')
    create table little_objects as select * from all_objects fetch first 5 rows only
    create table my_objects as select * from all_objects
/   
exec dbms_output.put_line('Current transaction='||dbms_transaction.local_transaction_id(true));

When we run this we get …

runtime_err

We can see that the local_transaction_id has changed. So the transaction that the CREATE SCHEMA was running in has ended. Question is – has it been commited or rolled back ?
Now to check if any of the tables have been created…

select table_name
from user_tables
where table_name in ('WTO_TERMS', 'LITTLE_OBJECTS')
/

no rows selected

We could go into great detail here and do some digging around in trace files.
Then again, there’s been quite enough procrastination around this whole Brexit business already.
As we can see, the functionality of CREATE SCHEMA is that it does execute multiple DDL statements in a single database transaction – i.e. all statements succeed or none do.
In other words, if it walks like a Brexit Monster and talks about sunlight uplands it’s probably Boris Johnson.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.