Databases - Ocient Documentation

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

SQL

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. `system` is a reserved database name and can neither be created nor dropped.

Example Create a new database named ocient.

SQL

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

SQL

DROP DATABASE [ IF EXISTS ] database_name [, ...]

Parameter	Type	Description
`database_name`	string	An 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.

SQL

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

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

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

SQL

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

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

SQL

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

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

SQL

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

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

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: * `password_minimum_length` * `password_complexity_level` * `password_no_repeat_count` * `password_lifetime_days` * `password_invalid_attempt_limit` For details about these values, see 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.

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

SQL

ALTER DATABASE example_db
    ALTER SECURITY password_lifetime_days = 20;

Core Elements of an Ocient System Data Query Language (DQL) Statement Reference Database Password Security Settings System Catalog

​CREATE DATABASE

​DROP DATABASE

​ALTER DATABASE

​ALTER DATABASE RENAME

​ALTER DATABASE SET SSO INTEGRATION

​ALTER DATABASE ALTER SSO INTEGRATION

​ALTER DATABASE REMOVE SSO INTEGRATION

​ALTER DATABASE ALTER SECURITY

​Related Links

CREATE DATABASE

DROP DATABASE

ALTER DATABASE

ALTER DATABASE RENAME

ALTER DATABASE SET SSO INTEGRATION

ALTER DATABASE ALTER SSO INTEGRATION

ALTER DATABASE REMOVE SSO INTEGRATION

ALTER DATABASE ALTER SECURITY

Related Links