Summary of DBMS_CLOUD_ADMIN Subprograms

This section covers the DBMS_CLOUD_ADMIN subprograms provided with Autonomous Data Warehouse.

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

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 Data Warehouse the service name consists of three parts:

  • database_name: the name of your database.
  • priority is one of: _high | _medium| _low
  • adwc.oraclecloud.com

You can find the service names in the tnsnames.ora file in the wallet.zip that you download from Autonomous Data Warehouse 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.

Supplying this argument is optional. If credential_name is not supplied or is supplied with a NULL value, the database link uses the connected user credentials (the link is created and uses the credentials, username and password, of the user who is accessing the link).

directory_name

The directory for the stored cwallet.sso file. The default value for this parameter is 'data_pump_dir'.

Usage Notes

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

GRANT EXECUTE ON DBMS_CLOUD_ADMIN TO adwc_user;
GRANT CREATE DATABASE LINK TO adwc_user;

Example

BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'DB_LINK_CRED', username => 'adwc_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.adwc.example.oraclecloud.com',
          ssl_server_cert_dn => 'CN=adwc.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 Data Warehouse.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_APP_CONT(
	service_name      IN VARCHAR2);

Parameters

Parameter Description

service_name

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

  • database_name: the name of your database.
  • priority is one of: _high | _medium| _low
  • adwc.oraclecloud.com

You can find the service names in the tnsnames.ora file in the wallet.zip that you download from Autonomous Data Warehouse 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.adwc.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.adwc.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 Data Warehouse.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_APP_CONT(
	service_name      IN VARCHAR2);

Parameters

Parameter Description

service_name

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

  • database_name: the name of your database.
  • priority is one of: _high | _medium| _low
  • adwc.oraclecloud.com

You can find the service names in the tnsnames.ora file in the wallet.zip that you download from Autonomous Data Warehouse 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.adwc.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.adwc.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 Data Warehouse 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

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 with Autonomous Data Warehouse 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;
/