Using CREATE SCHEMA statement in Oracle

This article is written about how to use the Oracle CREATE SCHEMA announcement with syntax and examples.

Description

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

The CREATE SCHEMA statement is used solely to create objects (ie: tables, views) in your schema in a single SQL statement, instead of having to problem man or woman CREATE TABLE statements and CREATE VIEW statements.

If an error happens 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.

Syntax

The syntax for the CREATE SCHEMA statement is:

CREATE SCHEMA AUTHORIZATION schema_name
    [create_table_statement]
    [create_view_statement]
    [grant_statement];

Parameters or Arguments

schema_name The title of the schema (which is the equal as your Oracle username that you are logged in as). create_table_statement Optional. It is a legitimate CREATE TABLE statement. create_view_statement Optional. It is a valid CREATE VIEW statement. grant_statement Optional. It is a valid GRANT statement.

Example

The following is a CREATE SCHEMA announcement (creating one desk inside 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 assertion creates a schema called smithj. In this new schema, it creates one desk called products.

You can also create more than one desk the use of the CREATE SCHEMA announcement 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 announcement would create two tables – merchandise and suppliers. If an error occurs developing both of these tables, neither desk will be created.

Alternatively, you should have created these two tables the use of 2 man or woman 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)
   );