Oracle SQL: the Essential Reference

CREATE SCHEMA

CREATE SCHEMA AUTHORIZATION schema [CREATE TABLE statement] [CREATE VIEW statement] [GRANT statement]

Creates multiple tables and/or views, and issues grants in a single statement.

Keywords

schema

Specifies the name of the schema to be created, which must be the same as your username.

CREATE TABLE

This is a CREATE TABLE statement, as shown later in this chapter.

CREATE VIEW

This is a CREATE VIEW statement, as shown later in this chapter.

GRANT

This is a GRANT statement, as shown later in this chapter.

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

Example

Create a schema for scott consisting of two tables and a view, and grant 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 TABLE emp (
      ename      VARCHAR2(20),
      deptno     NUMBER,
      sal        NUMBER (7,2),
      comm       NUMBER (7,2))
CREATE VIEW deptview AS SELECT deptno,dname,location FROM dept
GRANT SELECT ON deptview TO non_admin;