Summary of DBMS_CLOUD_ADMIN Subprograms

This section covers the DBMS_CLOUD_ADMIN subprograms provided with Autonomous Database.

Subprogram	Description
CREATE_DATABASE_LINK Procedure	This procedure creates a database link to a target database in the schema calling the API. You first need to upload the wallet (`cwallet.sso`) containing the certificates for the target database using `DBMS_CLOUD.GET_OBJECT` and then create the database link using the wallet.
DISABLE_APP_CONT Procedure	This procedure disables database application continuity for the session associated with the specified service name in Autonomous Database.
DROP_DATABASE_LINK Procedure	This procedure drops a database link.
ENABLE_APP_CONT Procedure	This procedure enables database application continuity for the session associated with the specified service name in Autonomous Database.
GRANT_TABLESPACE_QUOTA Procedure	This procedure grants a storage quota to a specified database user. When a tablespace quota is granted to a user, Autonomous Database limits the storage space used by that user to the specified quota. Using the value `UNLIMITED` specifies unlimited tablespace privilege.

CREATE_DATABASE_LINK Procedure

This procedure creates a database link to a target database in the schema calling the API. You first need to upload the wallet (cwallet.sso) containing the certificates for the target database using DBMS_CLOUD.GET_OBJECT and then create the database link using the wallet.

Syntax

DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
       db_link_name         IN VARCHAR2,
       hostname             IN VARCHAR2,
       port                 IN NUMBER,
       service_name         IN VARCHAR2,
       ssl_server_cert_dn   IN VARCHAR2, 
       credential_name      IN VARCHAR2,
       directory_name       IN VARCHAR2 DEFAULT,
       gateway_link         IN BOOLEAN DEFAULT);

Parameters

Parameter	Description
`db_link_name`	The name of the database link to create.
`hostname`	The hostname for the target database.
`port`	The port for the target database. To ensure security, ports are restricted to: `1521-1525`.
`service_name`	The `service_name` for the database to link to. For a target Autonomous Database the service name consists of three parts: `database_name`: the name of your database. priority: For example `_high`. The priority specifies a priority mapping for the connection service you use. `adb.oraclecloud.com` You can find the service names in the `tnsnames.ora` file in the `wallet.zip` that you download from the Autonomous Database for your connection.
`ssl_server_cert_dn`	The DN value found in the server certificate.
`credential_name`	The name of a stored credential created with `DBMS_CLOUD.CREATE_CREDENTIAL`. This is the credentials to access the target database.
`directory_name`	The directory for the stored `cwallet.sso` file. The default value for this parameter is `'data_pump_dir'.`
`gateway_link`	Indicates if the database link is created to another Oracle Database or to an Oracle Database Gateway. If `gateway_link` is set to `FALSE`, this specifies a database link to another Autonomous Database or to another Oracle Database. If `gateway_link` is set to `TRUE`, this specifies a database link to a non-Oracle system. This creates a connect descriptor in `tnsnames.ora` that specifies `(HS=OK)`. The default value for this parameter is `FALSE`.

Usage Notes

Autonomous Database supports creating database links only if the target database is accessible through a public IP or public hostname, or using an Oracle Database Gateway.
To run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK with a user other than ADMIN you need to grant EXECUTE and CREATE DATABASE LINK privileges to that user. For example, run the following command as ADMIN to grant privileges to adb_user:
```
GRANT EXECUTE ON DBMS_CLOUD_ADMIN TO adb_user;
GRANT CREATE DATABASE LINK TO adb_user;
```
Only one wallet file is valid per directory specified with the directory_name parameter. You can only upload one cwallet.sso at a time to the directory you choose for wallet files. This means with a cwallet.sso in a directory, you can only create database links to the databases for which the wallet in that directory is valid. To use multiple cwallet.sso files with database links you need to create additional directories and put each cwallet.sso in a different directory.

See Create Directory in Autonomous Database for information on creating directories.

To create a database link to an Autonomous Database, set GLOBAL_NAMES to FALSE on the source database (non-Autonomous Database).

SQL> ALTER SYSTEM SET GLOBAL_NAMES = FALSE;
 
System altered.

SQL> SHOW PARAMETER GLOBAL_NAMES
NAME                     TYPE        VALUE
----------------------   ----------- -----------
global_names             boolean     FALSE

Example

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'DB_LINK_CRED',
     username => 'adb_user',
     password => 'password');
  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
     db_link_name => 'SALESLINK', 
     hostname => 'adb.eu-frankfurt-1.oraclecloud.com', 
     port => '1522',
     service_name => 'example_medium.adb.example.oraclecloud.com',
     ssl_server_cert_dn => 'CN=adb.example.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US',
     credential_name => 'DB_LINK_CRED');
END;
/

DISABLE_APP_CONT Procedure

This procedure disables database application continuity for the session associated with the specified service name in Autonomous Database.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_APP_CONT(
	service_name      IN VARCHAR2);

Parameters

Parameter Description

Parameter	Description
`service_name`	The `service_name` for the Autonomous Database service. The service name consists of three parts: `database_name`: the name of your database. priority: For example `_high`. The priority specifies a priority mapping for the connection service you use. `adb.oraclecloud.com` You can find the service names in the `tnsnames.ora` file in the `wallet.zip` that you download from an Autonomous Database for your connection.

service_name

The service_name for the Autonomous Database service. The service name consists of three parts:

database_name: the name of your database.
priority: For example _high. The priority specifies a priority mapping for the connection service you use.
adb.oraclecloud.com

You can find the service names in the tnsnames.ora file in the wallet.zip that you download from an Autonomous Database for your connection.

Usage Notes

See Overview of Application Continuity for more information on Application Continuity.

Example

BEGIN
	DBMS_CLOUD_ADMIN.DISABLE_APP_CONT(
		service_name => 'nv123abc1_adb1_high.adb.oraclecloud.com' );
END;
/

Verify the value as follows:

SELECT name, failover_restore, drain_timeout FROM v$services;

NAME                                                    FAILOVER_RESTORE  DRAIN_TIMEOUT
------------------------------------------------------- ----------------- -------------
nv123abc1_adb1_high.adb.oraclecloud.com                 NONE                          0

DROP_DATABASE_LINK Procedure

This procedure drops a database link.

Syntax

DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(
	db_link_name      IN VARCHAR2);

Parameters

Parameter	Description
`db_link_name`	The name of the database link to drop.

Example

BEGIN
	DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(
		db_link_name => 'SALESLINK' );
END;
/

Usage Notes

After you are done using a database link and you run DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK, to ensure security of your Oracle database remove any stored wallet files. For example:

Remove the wallet file in Object Store.
Use DBMS_CLOUD.DELETE_FILE to remove the wallet file from the data_pump_dir directory or from the user defined directory where the wallet file was uploaded.

ENABLE_APP_CONT Procedure

This procedure enables database application continuity for the session associated with the specified service name in Autonomous Database.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_APP_CONT(
	service_name      IN VARCHAR2);

Parameters

Parameter Description

Parameter	Description
`service_name`	The `service_name` for the Autonomous Database service. The service name consists of three parts: `database_name`: the name of your database. priority: For example `_high`. The priority specifies a priority mapping for the connection service you use. `adb.oraclecloud.com` You can find the service names in the `tnsnames.ora` file in the `wallet.zip` that you download from an Autonomous Database for your connection.

service_name

The service_name for the Autonomous Database service. The service name consists of three parts:

database_name: the name of your database.
priority: For example _high. The priority specifies a priority mapping for the connection service you use.
adb.oraclecloud.com

You can find the service names in the tnsnames.ora file in the wallet.zip that you download from an Autonomous Database for your connection.

Usage Notes

See Overview of Application Continuity for more information on Application Continuity.

Example

BEGIN
	DBMS_CLOUD_ADMIN.ENABLE_APP_CONT(
		service_name => 'nvthp2ht_adb1_high.adb.oraclecloud.com'
	);
END;
/

Verify the value as follows:

SELECT name, failover_restore, drain_timeout FROM v$services;

NAME                                                    FAILOVER_RESTORE  DRAIN_TIMEOUT
------------------------------------------------------- ----------------- -------------
nvthp2ht_adb1_high.adb.oraclecloud.com                 LEVEL1                      300

GRANT_TABLESPACE_QUOTA Procedure

This procedure grants a storage quota to a specified database user. When a tablespace quota is granted to a user, Autonomous Database limits the storage space used by that user to the specified quota. Using the value UNLIMITED specifies unlimited tablespace privilege.

Syntax

DBMS_CLOUD_ADMIN.GRANT_TABLESPACE_QUOTA(
       username            IN VARCHAR2,
       tablespace_quota    IN VARCHAR2);

Parameters

Parameter Description

Parameter	Description
`user_name`	The database username to grant the tablespace quota to.
`tablespace_quota`	The quota to assign to the specified user in bytes. For kilobytes, megabytes, gigabytes, and terabytes you can specify `K`, `M`, `G`, or `T` after the numeric value respectively. Alternatively you can specify `UNLIMITED`, which is equivalent to `GRANT UNLIMITED TABLESPACE` with SQL.

user_name

The database username to grant the tablespace quota to.

tablespace_quota

The quota to assign to the specified user in bytes. For kilobytes, megabytes, gigabytes, and terabytes you can specify K, M, G, or T after the numeric value respectively.

Alternatively you can specify UNLIMITED, which is equivalent to GRANT UNLIMITED TABLESPACE with SQL.

Usage Notes

See System Privileges (Organized by the Database Object Operated Upon - TABLESPACE) for more information.

See Manage User Privileges on Autonomous Database - Connecting with a Client Tool for information on privileges granted with the role DWROLE.

Examples

BEGIN
	DBMS_CLOUD_ADMIN.GRANT_TABLESPACE_QUOTA(
           username => 'ADBUSER', 
           tablespace_quota => '10G'
	);
END;
/

BEGIN
	DBMS_CLOUD_ADMIN.GRANT_TABLESPACE_QUOTA(
           username => 'ADBUSER', 
           tablespace_quota => 'UNLIMITED'
	);
END;
/