CREATE SCHEMA
Oracle documentation states that the purpose of
CREATE SCHEMA is:
"Use the CREATE
SCHEMA statement to create multiple tables and views and
perform multiple grants in your own schema in a single transaction.
To execute a CREATE SCHEMA statement,
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."
The documentation goes on to note that a schema is automatically
created when a user is created. This statement will allow you to
"populate your schema with tables and views and grant privileges on
those objects without having to issue multiple SQL statements in
multiple transactions."
In order to issue a CREATE SCHEMA statement it is first necessary to
have privileges required to issue the included statements.
The Oracle documentation lists the following CREATE 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 the
SELECT object 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;
