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 has one or more objects. The username parameter specifies the owner of the schema. A schema and objects are created in a single transaction. Therefore, all the created objects include the schema. Otherwise, none of the created objects include the schema. Note: If you are using an Oracle database, no new schema (username) is created. Therefore, the schema must already exist.

A schema is a namespace that contains named objects such as tables and views. Different schemas may have the same named objects. You can access named objects by using either of the following methods: 1. Qualify the name of an object by using the schema name as the prefix. 2. Specify a search path that includes the required schema. Unqualified objects are created in the current schema (the schema before the search path, which can be determined by the CURRENT_SCHEMA function). The search paths and CURRENT_SCHEMA function are incompatible with Oracle databases.

The CREATE SCHEMA command includes subcommands to create objects within 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. Note: In PolarDB-O, the role and username must already exist, and the schema must not exist. In Oracle, a user that is equivalent to a schema must already 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 within the CREATE SCHEMA statement. After creating a schema, you can create other object types by using separate commands.

Example

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;