Defines a new schema.

Syntax

CREATE SCHEMA AUTHORIZATION username schema_element [ ... ]

Description

You can use the variant of the CREATE SCHEMA command to create a schema that is owned by a username and populated with one or more objects. A schema and objects are created in a single transaction. Therefore, all the created objects include the schema. Otherwise, no created objects include the schema.

A schema is a namespace that contains named objects, such as tables and views. The schema names may be the same as the names of other objects in other schemas. To access named objects, qualify the name of an object by using the schema name as the prefix or specify a search path that includes the required schema. Unqualified objects are created in the current schema that is the schema before the search path. This schema can be determined by the CURRENT_SCHEMA function. The search path concept and the CURRENT_SCHEMA function are incompatible with Oracle databases.

The CREATE SCHEMA command includes subcommands to create objects that are in the schema. Subcommands are processed in the same methods as separate commands that are issued after the schema is created. All the created objects are owned by the specified user.

Note To create a schema, you must have the CREATE privilege on the current database.

Parameters

Parameter Description
username The name of the user who owns the new schema. The schema name is the same as the username. Only superusers can create schemas that are owned by other users. Take note of the following item: In PolarDB O Edition, the role and the username must already exist and the schema must not exist. In Oracle, a user that is equivalent to a schema must exist.
schema_element An SQL statement that defines the objects to be created in the schema. You can use the CREATE TABLE, CREATE VIEW, and GRANT clauses in the CREATE SCHEMA statement. After a schema is created, you can create other object types by running separate commands.

Examples

CREATE SCHEMA AUTHORIZATION polardb
    CREATE TABLE empjobs (ename VARCHAR2(10), job VARCHAR2(9))
    CREATE VIEW managers AS SELECT ename FROM empjobs WHERE job = 'MANAGER'
    GRANT SELECT ON managers TO PUBLIC;