Oracle Database Administration: The Essential Refe

Name

CREATE SCHEMA

Synopsis

CREATE SCHEMA AUTHORIZATION schema
   [CREATE TABLE command]
   [CREATE VIEW command]
   [SQL_TRACE = {TRUE | FALSE} ]
   [GRANT command]

Creates multiple tables and views and performs multiple grants in a single transaction.

Keywords

CREATE TABLE

A CREATE TABLE command (described later in this chapter).

CREATE VIEW

A CREATE VIEW command (described later in this chapter).

SQL_TRACE

Controls whether performance statistics will be generated. The initial value is set in the INIT.ORA file.

GRANT

A GRANT command (described later in this chapter).

Notes

You must have the same privileges required for the CREATE TABLE, CREATE VIEW, and GRANT commands in order to issue this command. Individual commands within the CREATE SCHEMA command must not be terminated with the SQL termination character.

Example

The following example creates a schema for scott consisting of a table and a view, and grants privileges on the view to a role:

CREATE SCHEMA AUTHORIZATION scott
CREATE TABLE dept (
      deptno     NUMBER NOT NULL,
      dname      VARCHAR2(20),
      location   VARCHAR2(15),
      avg_salary number (9,2))
CREATE VIEW deptview AS SELECT deptno,dname,location FROM dept
GRANT SELECT ON deptview to non_admin