SCHEMA statement to create multiple tables and views and perform multiple grants in your own schema in a single transaction.
To execute a
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.
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.
SCHEMA statement can include
GRANT statements. To issue a
SCHEMA statement, you must have the privileges necessary to issue the included statements.
Specify the name of the schema. The schema name must be the same as your Oracle Database username.
CREATE SCHEMA supports
CREATE TABLE ,
CREATE BLOCKCHAIN TABLE is unsupported.
TABLE statement to be issued as part of this
CREATE SCHEMA statement. Do not end this statement with a semicolon (or other terminator character).
VIEW statement to be issued as part of this
SCHEMA statement. Do not end this statement with a semicolon (or other terminator character).
GRANT statement to be issued as part of this
SCHEMA 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
SCHEMA 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
GRANT statements is unimportant. The statements within a
SCHEMA statement can reference existing objects or objects you create in other statements within the same
Restriction on Granting Privileges to a Schema
The syntax of the
parallel_clause is allowed for a
TABLE statement in
SCHEMA, but parallelism is not used when creating the objects.
The parallel_clause in the
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 the
SELECT object privilege on
new_product_view to the sample human resources user
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;