SQL Statements: CREATE LIBRARY to CREATE SPFILE, 14 of 16

CREATE SCHEMA

Purpose

Use the CREATE SCHEMA to create multiple tables and views and perform multiple grants in a single transaction.

To execute a CREATE SCHEMA statement, Oracle executes each included statement. If all statements execute successfully, Oracle commits the transaction. If any statement results in an error, Oracle rolls back all the statements.

Note:

This statement does not actually create a schema. Oracle automatically creates a schema when you create a user (see CREATE USER ). This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.

Prerequisites

The CREATE SCHEMA statement can include CREATE TABLE, CREATE VIEW, and GRANT statements. To issue a CREATE SCHEMA statement, you must have the privileges necessary to issue the included statements.

Syntax

create_schema::=

Text description of statements_638.gif follows

Text description of create_schema

Keyword and Parameters

schema

Specify the name of the schema. The schema name must be the same as your Oracle username.

create_table_statement

Specify a CREATE TABLE statement to be issued as part of this CREATE SCHEMA statement. Do not end this statement with a semicolon (or other terminator character).

See Also:

CREATE TABLE

create_view_statement

Specify a CREATE VIEW statement to be issued as part of this CREATE SCHEMA statement. Do not end this statement with a semicolon (or other terminator character).

See Also:

CREATE VIEW

grant_statement

Specify a GRANT object_privileges statement to be issued as part of this CREATE SCHEMA statement. Do not end this statement with a semicolon (or other terminator character).

See Also:

GRANT

The CREATE SCHEMA statement supports the syntax of these statements only as defined by standard SQL, rather than the complete syntax supported by Oracle.

The order in which you list the CREATE TABLE, CREATE VIEW, and GRANT statements is unimportant. The statements within a CREATE SCHEMA statement can reference existing objects or objects you create in other statements within the same CREATE SCHEMA statement.

Restriction on Granting Privileges to a Schema

The syntax of the parallel_clause is allowed for a CREATE TABLE statement in CREATE SCHEMA, but parallelism is not used when creating the objects.

See Also:

the parallel_clause of CREATE TABLE

Example

Creating a Schema: Example

The following statement creates a schema named oe for the sample order-entry user oe, creates the table new_product, creates the view new_product_view, and grants SELECT privilege on new_product_view to the sample human resources user hr.

CREATE SCHEMA AUTHORIZATION oe
   CREATE TABLE new_product 
      (color VARCHAR2(10)  PRIMARY KEY, quantity NUMBER) 
   CREATE VIEW new_product_view 
      AS SELECT color, quantity FROM new_product WHERE color = 'RED' 
   GRANT select ON new_product_view TO hr;