Oracle/PLSQL: CREATE SCHEMA statement

The CREATE SCHEMA statement does NOT actually create a schema in Oracle. (Find out how to create a schema in Oracle.)

The CREATE SCHEMA statement is used only to create objects (ie: tables, views) in your schema in a single SQL statement, instead of having to issue individual CREATE TABLE statements and CREATE VIEW statements.

If an error occurs creating any of the objects in the CREATE SCHEMA statement, the Oracle database will roll back all create statements (e: tables and view) in the CREATE SCHEMA statement.

The syntax for the CREATE SCHEMA statement is:

CREATE SCHEMA AUTHORIZATION schema_name

    [create_table_statement]

    [create_view_statement]

    [grant_statement];

schema_name is the name of the schema (which is the same as your Oracle username that you are logged in as).

create_table_statement is optional. It is a valid CREATE TABLE statement.

create_view_statement is optional. It is a valid CREATE VIEW statement.

grant_statement is optional. It is a valid GRANT statement.

CREATE SCHEMA statement - Example

The following is a CREATE SCHEMA statement (creating one table within the schema):

CREATE SCHEMA AUTHORIZATION smithj

     CREATE TABLE products

        ( product_id number(10) not null,

          product_name varchar2(50) not null,

          category varchar2(50),

          CONSTRAINT products_pk PRIMARY KEY (product_id)

         );

This create schema statement creates a schema called smithj. In this new schema, it creates one table called products.

You can also create more than one table using the CREATE SCHEMA statement as follows:

CREATE SCHEMA AUTHORIZATION smithj

     CREATE TABLE products

        ( product_id number(10) not null,

          product_name varchar2(50) not null,

          category varchar2(50),

          CONSTRAINT products_pk PRIMARY KEY (product_id)

         )

     CREATE TABLE suppliers

        ( supplier_id number(10) not null,

          supplier_name varchar2(50) not null,

          city varchar2(25),

          CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)

         );

This CREATE SCHEMA statement would create two tables - products and suppliers. If an error occurs creating either of these tables, neither table will be created.

Alternatively, you could have created these 2 tables using 2 individual CREATE TABLE statements as follows (while logged in smithj):

CREATE TABLE products

   ( product_id number(10) not null,

     product_name varchar2(50) not null,

     category varchar2(50),

     CONSTRAINT products_pk PRIMARY KEY (product_id)

   );

CREATE TABLE suppliers

   ( supplier_id number(10) not null,

     supplier_name varchar2(50) not null,

     city varchar2(25),

     CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)

   );