Schemas

this group of ddl sql statements allows database administrators to manage schema design database administrators can create, modify, and drop schemas you can view information about the schema using the sys schemas system catalog table for information on other database components, see the pages on docid\ uy5crlqbr0ymvtqzwna6x , docid\ yhp4b1irv haf8f3df ww , docid\ g tzn2zxb4zp8w7za8a1k , and docid\ n7zyxmwfavae6fnmv2uvw implicit and explicit schemas the {{ocient}} system creates implicit schema objects when you execute the create or alter sql statements on a table, view, or machine learning model in the database, and there is no destination schema for those objects for example, in the alter table schema1 table1 rename to schema2 table1 statement, if schema2 does not exist in the database, the system creates schema2 as an implicit schema object the system creates explicit schema objects when you execute a create schema sql statement or promote an implicit schema using the alter schema sql statements or the grant sql statement on the schema to execute sql statements on objects or view the metadata of objects in an explicit schema, you must have the view schema privilege and the basic required privileges for the statement for an implicit schema, you do not need any additional privileges to create an object, if the schema is implicit, then you need the create object on database privilege, where object can be a table, view, or machine learning model if the schema is explict, then you need the create object on schema privilege you can check the disposition of the schema using the is implicit column in the sys schemas system catalog table after promotion, you cannot demote an explicit schema back to an implicit one create schema create schema defines an explicit schema to use the optional or replace sql statement, the schema must not contain other objects required privileges users with the create schema privilege on the database can create an explicit schema after you create an explicit schema, ensure to specify the privileges for each user for details, see docid\ f55ngxtki0f7kkmyatvug syntax create \[ or replace ] schema \[ if not exists ] schema name parameter type description schema name string an identifier for the schema to create the name must be distinct from any existing schema in the database unless you specify the replace keyword sys , sysgdc , syslookup , and information schema are reserved schema names and can neither be created nor dropped example define the sandbox schema create schema sandbox; alter schema rename alter schema rename renames an existing schema if you use this statement with an implicit schema, the system promotes the implicit schema to an explicit schema required privileges to rename a schema , you must have the view and alter privileges for the schema the ocient system requires these privileges if this statement includes a change to the schema view and alter privilege on the current schema create schema privilege on the database syntax alter schema \[ if exists ] old schema name rename to new schema name parameter type description old schema name string the old identifier of the schema for rename new schema name string the new identifier of the schema for rename the name must be distinct from the name of any existing schema in the system example rename an existing schema named sandbox to ocient alter schema sandbox rename to ocient; drop schema drop schema removes an existing schema the ocient system automatically removes an implicit schema when you remove all its contents however, an explicit schema persists even without any objects if the existing schema contains objects and you do not specify the cascade keyword, the system cannot remove this schema otherwise, the system removes all objects inside the existing schema required privileges if the schema is explicit, you must have the drop schema privilege otherwise, if the schema is implicit, the schema must not contain any objects, or if you specify the cascade keyword, you must have the view and drop privileges for all objects in the schema to remove it syntax drop schema \[ if exists ] schema name \[, ] \[ cascade ] parameter type description schema name string an identifier for the schema to remove you can drop multiple schemas by specifying additional schema names and separating each with commas sys , sysgdc , syslookup , and information schema are reserved schema names and can neither be created nor dropped example remove an existing schema named ocient drop schema ocient; related links docid nwuyof4 i 7wgmmmbf4j docid\ nw9vavkey2v75moxm muo docid\ jfltms91v2bfledppksy8 https //docs ocient com/system catalog