CREATE SCHEMA

Purpose

Use the CREATESCHEMA statement to create multiple tables and views and perform multiple grants in your own schema in a single transaction.

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

Note:

This statement does not actually create a schema. Oracle Database 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 CREATESCHEMA statement can include CREATETABLE, CREATEVIEW, and GRANT statements. To issue a CREATESCHEMA statement, you must have the privileges necessary to issue the included statements.

Specify a GRANT statement to be issued as part of this CREATESCHEMA statement. Do not end this statement with a semicolon (or other terminator character). You can use this clause to grant object privileges on objects you own to other users. You can also grant system privileges to other users if you were granted those privileges WITHADMINOPTION.

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

The order in which you list the CREATETABLE, CREATEVIEW, and GRANT statements is unimportant. The statements within a CREATESCHEMA statement can reference existing objects or objects you create in other statements within the same CREATESCHEMA statement.

Restriction on Granting Privileges to a Schema

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

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 the SELECT object privilege on new_product_view to the sample human resources user hr.