CREATE SCHEMA: a SQL curiosity

Pete Finnegan picked up on my recent piece USER != SCHEMA and linked to his article on the CREATE SCHEMA statement. This was prescient on his part, as I had decided against discussing it statement in that article (for reasons of length). There is nothing wrong with Pete's piece but I thought expanding on CREATE SCHEMA might be helpful, as one of the other people who commented seemed confused about it.

And who wouldn't be? For start, it's not really CREATE SCHEMA, it's CREATE SCHEMA AUTHORIZATION. ....

SQL> create schema c
  2  create table t3 (col1 number, col2 number)
  3  /
create schema c
              *
ERROR at line 1:
ORA-02420: missing schema authorization clause

SQL>

Furthermore, we are not creating a schema, we are adding new objects to a pre-existing schema.

SQL> create schema authorization c
  2  create table t3 (col1 number, col2 number)
  3  /
create schema authorization c
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier


SQL> 

The schema authorization identifier is invalid because my database does not have a user C. So let's try again with good ol' user A, who already has some objects in his schema.

SQL> select object_type, object_name from user_objects
  2  where object_type in ('TABLE', 'VIEW')
  3  /
OBJECT_TYPE        OBJECT_NAME
------------------ ----------------
TABLE              T1
TABLE              T2
VIEW               V1

3 rows selected.

SQL> create schema authorization a
  2  create view v2 as select * from t2
  3  /

Schema created.

SQL> 

A misleading response there: the schema already existed. But I suppose "Schema authorization applied" is a bit of a mouthful.

The cool thing about CREATE SCHEMA is that we can put together several CREATE statements and run them as a single transaction. So if one of the CREATE statements fails they all fail. It's the closest Oracle gets to being able to rollback DDL statements.

SQL> create schema authorization a
  2  create table t3 (col1 number, col2 number)
  3  create table t1  (col1 number, col2 number)
  4  /
create table t1  (col1 number, col2 number)
*
ERROR at line 3:
ORA-02425: create table failed
ORA-00955: name is already used by an existing object

SQL> desc t3
ERROR:
ORA-04043: object t3 does not exist

SQL> 

Unlike with the table statement a CREATE VIEW exception doesn't give out the underlying error when it fails (at least in 9.2.0.6)....

SQL> create schema authorization a
  2  create table t3 (col1 number, col2 number)
  3  create view v1 as select * from b.t1
  4  /
create schema authorization a
*
ERROR at line 1:
ORA-02427: create view failed
SQL> 

As it happens we know the view already exists, so let's presume the error is ORA-955. Normally we could work around that with CREATE OR REPLACE VIEW but ...

SQL> create schema authorization a
  2  create table t3 (col1 number, col2 number)
  3  create or replace  view v1 as select * from b.t1
  4  /
create or replace  view v1 as select * from b.t1
       *
ERROR at line 3:
ORA-02422: missing or invalid schema element

SQL> 

CREATE SCHEMA is a "contractual obligation" command: Oracle has it because the ANSI standard says it has to be there. In Oracle SQL only three commands are supported: CREATE TABLE, CREATE VIEW and GRANT. (In DB2 the statement also supports creating indexes). It also only supports standard SQL, so there are some proprietary Oracle SQL which will cause the statement to hurl. CREATE SCHEMA is actually very restricted in its scope and consequently is of limited usefulness. I don't think it can replace a proper regression script for doing database deployments.

There is one last gotcha. Although the CREATE statements bundled with the CREATE SCHEMA statement are transactional and appear to be rollback-able, the statement itself is still plain DDL and issues the implicit commit before it executes.

SQL> insert into t1 values (566, 888)
  2  /

1 row created.

SQL> create schema authorization a
  2  create table t3 (col1 number, col2 number)
  3  create   view v1 as select * from b.t1
  4  /
create schema authorization a
*
ERROR at line 1:
ORA-02427: create view failed

SQL> rollback
  2  /

Rollback complete.

SQL> select * from t1
  2  /
      COL1 COL2
---------- ----------
       566 888
         1 AAAAAAAAAA

SQL> 

Now, the ability to suspend the transactionality of individual DDLs would be quite helpful. My last installation (which was a change to an existing system) required the deployment of two new schemas with over a hundred tables each, plus indexes, several hundred types, procedures, packages and bodies, not to mention a number of changes to existing schemas. It would be nice to be able to rollback such gargantuan deployments if something goes wrong. But, even if the syntax allowed it, CREATE SCHEMA is not appropriate. Putting all that into a CREATE SCHEMA statement would have resulted in a command almost 2MB long. Debug that! The coming 11g Editioning feature (caveat: BETA!) strikes me as a more practical alternative.

So, CREATE SCHEMA looks like the SQL equivalent of the human appendix. It's there but it doesn't really do anything useful.