---
title: Schemas
slug: M9Zb-sc
description: Learn about the SQL syntaxes for managing schemas in Ocient for efficient data organization.
docTags: 
createdAt: Mon Aug 11 2025 18:02:14 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:30 GMT+0000 (Coordinated Universal Time)
---

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 [Databases](./Databases.md), [Tables](./Tables.md), [Views](./Views.md), and [Indexes](./Indexes.md).

# 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.

:::hint{type="info"}
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.&#x20;

After you create an explicit schema, ensure to specify the privileges for each user. For details, see [Data Control Language (DCL) Statement Reference](<././Data Control Language _DCL_ Statement Reference.md>).&#x20;

**Syntax**

```sql
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. <br /><br />`sys`, `sysgdc`, `syslookup`, and `information_schema` are reserved schema names and can neither be created nor dropped. |

**Example**

Define the `sandbox` schema.

```sql
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**

```sql
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`.

```sql
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.&#x20;

**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**

```sql
DROP SCHEMA [ IF EXISTS ] schema_name [, ...] [ CASCADE ]
```

| **Parameter** | **Type** | **Description**                                                                                                                                                                                                                                                                               |
| ------------- | -------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `schema_name` | string   | An identifier for the schema to remove.<br /><br />You can drop multiple schemas by specifying additional schema names and separating each with commas. <br /> <br />`sys`, `sysgdc`, `syslookup`, and `information_schema` are reserved schema names and can neither be created nor dropped. |

**Example**

Remove an existing schema named `ocient`.

```sql
DROP SCHEMA ocient;
```

# Related Links

[Core Elements of an Ocient System](<./././Ocient Architecture/Core Elements of an Ocient System.md>)&#x20;

[General SQL Syntax](<./././Query Ocient/General SQL Syntax.md>)

[Database Password Security Settings](<./././Database Administration/Authentication Methods/Database Password Security Settings.md>)

[System Catalog](https://docs.ocient.com/system-catalog)
