Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
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. |
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.
create_schema::=
Specify the name of the schema. The schema name must be the same as your Oracle username.
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).
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).
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).
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.
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 |
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;