This topic describes how to create a schema.
CREATE SCHEMA AUTHORIZATION username schema_element [ ... ];
|username||The name of the user who will own the new schema.
The schema will be named the same as username.
|schema_element||An SQL statement defining an object to be created within the schema.
CREATE TABLE, CREATE VIEW, and GRANT are accepted as clauses within CREATE SCHEMA. Other kinds of objects may be created in separate statements after the schema is created.
This variation of the CREATE SCHEMA statement creates a new schema owned by username and populated with one or more objects. The creation of the schema and objects occur within a single transaction so either all objects are created or none of them including the schema.
A schema is essentially a namespace. It contains named objects such as tables and views whose names may duplicate those of other objects existing in other schemas. Named objects are accessed either by qualifying their names with the schema name as a prefix, or by setting a search path that includes the desired schemas. Unqualified objects are created in the current schema. The schema at the front of the search path can be determined with the CURRENT_SCHEMA function. The search path concept and the CURRENT_SCHEMA function are not compatible with Oracle databases.
CREATE SCHEMA includes sub-statements to create objects within the schema. The sub-statements are treated essentially the same as separate statements issued after creating the schema. All the created objects will be owned by the specified user.
CREATE SCHEMA AUTHORIZATION enterprisedb 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;