Oracle CREATE SCHEMA operator

The CREATE SCHEMA operator does not actually create the circuit in Oracle. (Learn how to create a schema in Oracle.)

The CREATE SCHEMA statement is only used to create objects (i.e. tables, views) in your schema in one SQL statement instead of running the individual CREATE TABLE and CREATE VIEW statements.

If an error occurs when creating any of the objects in the CREATE SCHEMA statement, the Oracle database rolls back all creation operations (i.e. tables and views) in the CREATE SCHEMA statement.

Syntax of the CREATE SCHEMA operator

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

Parameters and arguments of the operator

• schema_name – the name of the schema (which is the same as the Oracle user you logged on with).
• create_table_statement – optional. This is a valid CREATE TABLE operator.
• create_view_statement is optional. This is a valid CREATE VIEW operator.
• grant_statement is optional. This is a valid GRANT operator.

Below is the CREATE SCHEMA operator (creating one table in the schematic):

CREATE SCHEMA AUTHORIZATION samvel
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)
);

In this example, a scheme called samvel is created. In this new scheme, one table is created, called products.

You can also create multiple tables using the CREATE SCHEMA operator as follows:

CREATE SCHEMA AUTHORIZATION samvel
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 operator will create two tables, the products and the suppliers. If an error occurs when creating any of these tables, no table will be created.

Alternatively, you could create these 2 tables using two separate CREATE TABLE operators as follows (as long as you are a samvel user):

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)
);

What is an Oracle Schema – Database Tutorial – Oracle DBA Tutorial