> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Databases

export const Ocient = "Ocient®";

This group of DDL SQL statements allows database administrators to manage databases. Database administrators can create and modify databases, including SSO authentication settings. You can view information about databases using the `sys.databases` system catalog table.

For information on database components, see the pages on [Schemas](/schemas), [Tables](/tables), [Views](/views), and [Indexes](/indexes).

## CREATE DATABASE

`CREATE DATABASE` creates a new database. The database name must be distinct from the name of any existing database in the system.

To create a database, you must have the `CREATE DATABASE` privilege for the current system.

**Syntax**

```sql SQL theme={null}
CREATE DATABASE [ IF NOT EXISTS ] database_name
```

| **Parameter**   | **Type** | **Description**                                                                                                                                                                        |
| --------------- | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `database_name` | string   | A unique identifier for the database. The system generates an error if you specify a duplicate name.<br />`system` is a reserved database name and can neither be created nor dropped. |

**Example**

Create a new database named `ocient`.

```sql SQL theme={null}
CREATE DATABASE ocient;
```

## DROP DATABASE

`DROP DATABASE` removes an existing database. This SQL statement also disconnects all users currently connected to the database.

To remove a database, you must have the `DROP DATABASE` privilege for the current database.

You cannot drop a database while it has any `PIPELINE` in a running status.

<Warning>
  The `DROP DATABASE` SQL statement removes the existing database and all created users, tables, and views.

  This action cannot be undone.
</Warning>

**Syntax**

```sql SQL theme={null}
DROP DATABASE [ IF EXISTS ] database_name [, ...]
```

| **Parameter**   | **Type** | **Description**                                                                                                                                                                                                                                |
| --------------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `database_name` | string   | An identifier for the database to be dropped.<br />You can drop multiple databases by specifying additional database names and separating each with commas. <br />`system` is a reserved database name and can neither be created nor dropped. |

**Example**

Remove an existing database named `ocient`.

```sql SQL theme={null}
DROP DATABASE ocient;
```

## ALTER DATABASE

### ALTER DATABASE RENAME

`ALTER DATABASE RENAME` renames an existing database.

To rename a database, you must have the `ALTER DATABASE` privilege for the database.

**Syntax**

```sql SQL theme={null}
ALTER DATABASE old_database_name RENAME TO new_database_name
```

| **Parameter**       | **Type** | **Description**                                    |
| ------------------- | -------- | -------------------------------------------------- |
| `old_database_name` | string   | The old identifier of the database for the rename. |
| `new_database_name` | string   | The new identifier of the database for the rename. |

**Example**

Rename an existing database named `oracle` to `ocient`.

```sql SQL theme={null}
ALTER DATABASE oracle RENAME TO ocient;
```

### ALTER DATABASE SET SSO INTEGRATION

`ALTER DATABASE SET SSO INTEGRATION` configures the database to authenticate using an external SSO provider. This SSO integration is the default for connections unless you use a connectivity pool or specify a different provider.

To set a connection, you must be a system-level user or a database administrator and have an open connection to the database.

This SQL statement is an alias for [ALTER DATABASE ALTER SSO INTEGRATION](#alter-database-alter-sso-integration).

See [Configuring the Ocient Database](/authentication-methods#sso-parameters) for details about configuring SSO protocols.

<Info>
  If your {Ocient} System is version 25.0 or later, you can create multiple SSO integrations for each database. An SSO integration assigned to the database by the `ALTER DATABASE` SQL statement is the primary SSO connection, unless you connect to the database with a connectivity pool that has a different SSO integration assigned to it.
</Info>

**Syntax**

```sql SQL theme={null}
ALTER DATABASE database
    ALTER SSO INTEGRATION sso_name
```

| **Parameter** | **Type** | **Description**                                   |
| ------------- | -------- | ------------------------------------------------- |
| `database`    | string   | The identifier of the database for configuration. |
| `sso_name`    | string   | The identifier of the SSO integration to use.     |

**Example**

This example sets an example database to use the SSO integration named `sso_test`.

```sql SQL theme={null}
ALTER DATABASE example_database
    SET SSO INTEGRATION sso_test;
```

### ALTER DATABASE ALTER SSO INTEGRATION

`ALTER DATABASE ALTER SSO CONNECTION` configures the database to authenticate using an external SSO provider. This SSO integration is the default for connections unless you use a connectivity pool or specify a different provider.

To alter a connection, you must be a system-level user or a database administrator and have an open connection to the database.

This SQL statement is an alias for [ALTER DATABASE SET SSO INTEGRATION](#alter-database-set-sso-integration).

See [Configuring the Ocient Database](/authentication-methods) for details about configuring SSO protocols.

<Info>
  If your Ocient System is version 25.0 or later, you can create multiple SSO integrations for each database. An SSO integration assigned to the database by the `ALTER DATABASE` SQL statement is the primary SSO connection, unless you connect to the database with a connectivity pool that has a different SSO integration assigned to it.
</Info>

**Syntax**

```sql SQL theme={null}
ALTER DATABASE database
    ALTER SSO INTEGRATION sso_name
```

| **Parameter** | **Type** | **Description**                                   |
| ------------- | -------- | ------------------------------------------------- |
| `database`    | string   | The identifier of the database for configuration. |
| `sso_name`    | string   | The identifier of the SSO integration to use.     |

**Example**

This example alters an example database to use the SSO integration named `sso_test`.

```sql SQL theme={null}
ALTER DATABASE example_database
    ALTER SSO INTEGRATION sso_test;
```

### ALTER DATABASE REMOVE SSO INTEGRATION

`ALTER DATABASE REMOVE SSO INTEGRATION` removes an existing SSO integration as the default connection protocol for the database. This action effectively undoes the  [ALTER DATABASE ALTER SSO INTEGRATION](#alter-database-alter-sso-integration) SQL statement.

To remove a connection, you must be a system-level user or a database administrator.

**Syntax**

```sql SQL theme={null}
ALTER DATABASE database REMOVE SSO INTEGRATION
```

| **Parameter** | **Type** | **Description**                              |
| ------------- | -------- | -------------------------------------------- |
| `database`    | string   | The identifier of the database for deletion. |

**Example**

Remove the default connection from the database named `example_database`.

```sql SQL theme={null}
ALTER DATABASE example_database REMOVE SSO INTEGRATION;
```

### ALTER DATABASE ALTER SECURITY

Sets the security settings at the database level using the `ALTER DATABASE ALTER SECURITY` SQL statement. Replace `<security_setting>` with the security setting and `<value>` with the value.

**Syntax**

```sql SQL theme={null}
ALTER DATABASE database ALTER SECURITY <security_setting> [=] <value>
```

| **Parameter**      | **Data** **Type** | **Description**                                                                                                                                                                                                                                                                                                                            |
| ------------------ | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `database`         | string            | The identifier of the database for setting security settings.                                                                                                                                                                                                                                                                              |
| `security_setting` | string            | The security setting with values:<br />\* `password_minimum_length`<br />\* `password_complexity_level`<br />\* `password_no_repeat_count`<br />\* `password_lifetime_days`<br />\* `password_invalid_attempt_limit`<br />For details about these values, see [Database Password Security Settings](/database-password-security-settings). |
| `value`            | numeric           | An integer to represent one of the security settings. For details about this value, see [Database Password Security Settings](/database-password-security-settings).                                                                                                                                                                       |

**Example**

Set the password lifetime to 20 days for the database `example_db`.

```sql SQL theme={null}
ALTER DATABASE example_db
    ALTER SECURITY password_lifetime_days = 20;
```

## Related Links

[Core Elements of an Ocient System](/core-elements-of-an-ocient-system)

[General SQL Syntax](/general-sql-syntax)

[Database Password Security Settings](/database-password-security-settings)

[System Catalog](/system-catalog)