Skip to navigation
SQL ReferenceData Definition Language (DDL) Statement Reference

Databases

View as Markdown

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, Tables, Views, and 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

CREATE DATABASE [ IF NOT EXISTS ] database_name
ParameterTypeDescription
database_namestringA unique identifier for the database. The system generates an error if you specify a duplicate name.
system is a reserved database name and can neither be created nor dropped.

Example

Create a new database named ocient.

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.

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

This action cannot be undone.

Syntax

DROP DATABASE [ IF EXISTS ] database_name [, ...]
ParameterTypeDescription
database_namestringAn identifier for the database to be dropped.
You can drop multiple databases by specifying additional database names and separating each with commas.
system is a reserved database name and can neither be created nor dropped.

Example

Remove an existing database named ocient.

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

ALTER DATABASE old_database_name RENAME TO new_database_name
ParameterTypeDescription
old_database_namestringThe old identifier of the database for the rename.
new_database_namestringThe new identifier of the database for the rename.

Example

Rename an existing database named oracle to ocient.

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.

See Configuring the Ocient Database for details about configuring SSO protocols.

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.

Syntax

ALTER DATABASE database
    ALTER SSO INTEGRATION sso_name
ParameterTypeDescription
databasestringThe identifier of the database for configuration.
sso_namestringThe identifier of the SSO integration to use.

Example

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

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.

See Configuring the Ocient Database for details about configuring SSO protocols.

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.

Syntax

ALTER DATABASE database
    ALTER SSO INTEGRATION sso_name
ParameterTypeDescription
databasestringThe identifier of the database for configuration.
sso_namestringThe identifier of the SSO integration to use.

Example

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

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 SQL statement.

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

Syntax

ALTER DATABASE database REMOVE SSO INTEGRATION
ParameterTypeDescription
databasestringThe identifier of the database for deletion.

Example

Remove the default connection from the database named example_database.

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

ALTER DATABASE database ALTER SECURITY <security_setting> [=] <value>
ParameterData TypeDescription
databasestringThe identifier of the database for setting security settings.
security_settingstringThe security setting with values:
* password_minimum_length

Example

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

ALTER DATABASE example_db
    ALTER SECURITY password_lifetime_days = 20;

Related Links

Core Elements of an Ocient System

General SQL Syntax

Database Password Security Settings

System Catalog