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 :
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…
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…
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
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 …
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.