Oracle7 Server SQL Reference

CREATE SCHEMA

Purpose

To create multiple tables and views and perform multiple grants in a single transaction.

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

Keyword and Parameters

schema

is the name of the schema. The schema name must be the same as your Oracle7 username.

CREATE TABLE command

is a CREATE TABLE statement to be issued as part of this CREATE SCHEMA statement

. See the CREATE TABLE command .

CREATE VIEW command

is a CREATE VIEW statement

to be issued as part of this CREATE SCHEMA statement. See the CREATE VIEW command .

GRANT command

is a GRANT statement (Objects Privileges) to be issued as part of this CREATE SCHEMA statement. See the GRANT command .

The CREATE SCHEMA statement only supports the syntax of these commands as defined by standard SQL, rather than the complete syntax supported by Oracle7. For information on which parts of the syntax for these commands are standard SQL and which are Oracle7 extensions, see Appendix B of this manual.

Usage Notes

With the CREATE SCHEMA command, you can issue multiple Data Definition Language statements in a single transaction. To execute a CREATE SCHEMA statement, Oracle7 executes each included statement. If all statements execute successfully, Oracle7 commits the transaction. If any statement results in an error, Oracle7 rolls back all the statements.

Terminate a CREATE SCHEMA statement just as you would any other SQL statement using the terminator character specific to your tool. For example, if you issue a CREATE SCHEMA statement in SQL*Plus or Server Manager, terminate the statement with a semicolon (;). Do not separate the individual statements within a CREATE SCHEMA statement with the terminator character.

The order in which you list the CREATE TABLE, CREATE VIEW, and GRANT statements is unimportant:

A CREATE VIEW statement can create a view that is based on a table that is created by a later CREATE TABLE statement.

A CREATE TABLE statement can create a table with a foreign key that depends on the primary key of a table that is created by a later CREATE TABLE statement.

A GRANT statement can grant privileges on a table or view that is created by a later CREATE TABLE or CREATE VIEW statement.

The statements within a CREATE SCHEMA statement can also reference existing objects:

A CREATE VIEW statement can create a view on a table that existed before the CREATE SCHEMA statement.

A GRANT statement can grant privileges on a previously existing object.

PARALLEL Clause Syntax

The syntax of the PARALLEL clause is allowed for a CREATE TABLE, INDEX, or CLUSTER, when used in CREATE SCHEMA, but parallelism is not used when creating the objects.

Example

The following statement creates a schema named BLAIR for the user BLAIR:

CREATE SCHEMA AUTHORIZATION blair 
	CREATE TABLE sox 
		(color VARCHAR2(10)  PRIMARY KEY, quantity NUMBER) 
	CREATE VIEW red_sox 
		AS SELECT color, quantity FROM sox WHERE color = 'RED' 
	GRANT select ON red_sox TO waites

The following statement creates the table SOX, creates the view RED_SOX, and grants SELECT privilege on the RED_SOX view to the user WAITES.