DBMS_CLOUD_ADMIN Subprograms

This section covers the DBMS_CLOUD_ADMIN subprograms provided with Autonomous AI Database.

ATTACH_FILE_SYSTEM Procedure

This procedure attaches a file system in the database.

The DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM procedure attaches a file system in your database and stores information about the file system in the DBA_CLOUD_FILE_SYSTEMS view.

Syntax

DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM (
    file_system_name         IN VARCHAR2,
    file_system_location     IN VARCHAR2,
    directory_name           IN VARCHAR2,
    description              IN VARCHAR2 DEFAULT NULL,
    params                   IN CLOB DEFAULT NULL
);

Parameters

Examples

Attach to an NFSv3 file system:

BEGIN
   DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM (
    file_system_name      => 'FSS',
    file_system_location  => 'myhost.sub000445.myvcn.oraclevcn.com:/results',
    directory_name        => 'FSS_DIR',
    description           => 'Source NFS for sales data'
  );
END;
/

Attach to an NFSv4 file system:

BEGIN
   DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM (
    file_system_name      => 'FSS',
    file_system_location  => 'myhost.sub000445.myvcn.oraclevcn.com:/results',
    directory_name        => 'FSS_DIR',
    description           => 'Source NFS for sales data',
    params                => JSON_OBJECT('nfs_version' value 4)
);
END;
/

Usage Note

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• You must have WRITE privilege on the directory object in the database to attach a file system using DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM.

• The DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM procedure can only attach a private File Storage Service in databases with Private Endpoints enabled.

  See OCI File Storage Service andConfigure Network Access with Private Endpoints for more information.

• The DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM procedure looks up the Network File System hostname on the customer's virtual cloud network (VCN). The error "ORA-20000: Mounting NFS fails" is returned if the hostname specified in the location cannot be located.

• Oracle Cloud Infrastructure File Storage uses NFSv3 to share

• If you attach to non-Oracle Cloud Infrastructure File Storage systems, the procedure supports NFSv3 and NFSv4

• If you have an attached NFS server that uses NFSv3 and the NFS version is updated to NFSv4 in the NFS server, you must run DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM and then DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM (using the params parameter with nfs_version set to 4). This attaches NFS with the matching protocol so that Autonomous AI Database can access the NFSv4 Server. Without detaching and then reattaching, the NFS server will be inaccessible and you may see an error such as: "Protocol not supported".

CANCEL_WORKLOAD_CAPTURE Procedure

This procedure cancels any ongoing workload capture on the database.

Syntax

This procedure cancels the current workload capture and enables refresh on the refreshable clone.

DBMS_CLOUD_ADMIN.CANCEL_WORKLOAD_CAPTURE;

Example

BEGIN
   DBMS_CLOUD_ADMIN.CANCEL_WORKLOAD_CAPTURE;
END;
/

Usage Note

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

CREATE_DATABASE_LINK Procedure

This procedure creates a database link to a target database in the schema calling the API.

The overloaded forms support the following:

• When you use the gateway_params parameter, this enables you to create a database link with Oracle-managed heterogeneous connectivity where the link is to a supported non-Oracle database.

• When you use the rac_hostnames parameter, this enables you to create a database link from an Autonomous AI Database on a private endpoint to a target Oracle RAC database. In this case, you use the rac_hostnames parameter to specify the host names of one or more individual nodes of the target Oracle RAC database.

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 DEFAULT,
       credential_name      IN VARCHAR2 DEFAULT,
       directory_name       IN VARCHAR2 DEFAULT,
       gateway_link         IN BOOLEAN DEFAULT,
       public_link          IN BOOLEAN DEFAULT,
       private_target       IN BOOLEAN DEFAULT
       gateway_params       IN CLOB DEFAULT);

DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
       db_link_name         IN VARCHAR2,
       rac_hostnames        IN CLOB,
       port                 IN NUMBER,
       service_name         IN VARCHAR2,
       ssl_server_cert_dn   IN VARCHAR2 DEFAULT,
       credential_name      IN VARCHAR2 DEFAULT,
       directory_name       IN VARCHAR2 DEFAULT,
       gateway_link         IN BOOLEAN DEFAULT,
       public_link          IN BOOLEAN DEFAULT,
       private_target       IN BOOLEAN DEFAULT);

Parameters

'["sales1-svr1.domain", "sales1-svr2.domain", "sales1-svr3.domain"]'

gateway_params => JSON_OBJECT( 'db_type' value 'snowflake', 'enable_ssl' value true)

Usage Notes

• When you specify the gateway_params parameter, for some db_type values, additional gateway_params parameters are supported:

  db_type	Additional gateway_params Values
  azure	When the db_type value is azure there are two optional parameters, auth_method and domain to support NTLM/AD authentication. When NTLMv2 is configured, set auth_method=10 and set domain to the Windows domain value. To use an Azure Active Directory password, set auth_method=13. Do not use auth_method=13 to access an Oracle on-premises Database. See Microsoft SQL Server Authentication Method for more information.
  google_analytics	When the db_type is google_analytics, the credential you specify must be a Google OAuth credential (gcp_oauth2). See CREATE_CREDENTIAL Procedure for more information.
  google_bigquery	When the db_type is google_bigquery, the credential you specify must be a Google OAuth credential (gcp_oauth2) See CREATE_CREDENTIAL Procedure for more information. When db_type is google_bigquery, the parameter project is valid. This parameter specifies the project name for google_bigquery and is required. The table name you specify when you use SELECT with Google BigQuery must be in quotes. For example: SELECT * FROM "sales"@GOOGLE_BIGQUERY_LINK
  google_drive	For db_type google_drive, the credential you supply with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK must be an OAuth type credential using the params parameter with gcp_oauth2 values specified (client_id, client_secret, and refresh_token). To connect to Google Drive and get data you must supply the gateway parameters directory_name and file_name. These parameters specify a model file (auto REST file) that maps the JSON response to the relational model. The model file specifies the endpoints, table mapping, and HTTP response code for processing the JSON response. See Model file syntax and Example Model file for more information. When you use gateway_params parameter with db_type google_drive you must supply the gateway_params parameter db_type with the value 'google_drive' and the directory_name and file_name parameters, along with OAuth type credentials: The directory_name parameter specifies the directory with the REST config file. You could create this directory as follows: create or replace directory google_drive_dir as 'GOOGLE_DRIVE_DIR'; Obtain and download the auto REST file to the specified directory. For example: exec DBMS_CLOUD.get_object('google_drive_dir_cred', 'https://objectstorage.<...>/google_drive.rest','GOOGLE_DRIVE_DIR'); Set the file_name value to the name of the auto REST file you downloaded, "google_drive.rest". Then you can use the REST config file with OAuth 2.0.
  hive	When db_type is hive, the parameter http_path is valid. This parameter specifies the HttpPath value, if required, to connect to the Hive instance.
  salesforce	When the db_type is salesforce, the parameter: security_token is valid. A security token is a case-sensitive alphanumeric code. Supplying a security_token value is required to access Salesforce. For example: gateway_params => JSON_OBJECT( 'db_type' value 'salesforce', 'security_token' value 'security_token_value' ) See Reset Your Security Token for more information.
  servicenow	To connect to ServiceNow and get data you must supply the gateway parameters directory_name and file_name. These parameters specify a model file (REST config file) that maps the JSON response to the relational model. The model file specifies the endpoints, table mapping, and HTTP response code for processing the JSON response. See Model file syntax and Example Model file for more information. When you use gateway_params parameter with db_type servicenow, there are two supported options: Basic Authentication: you must supply the gateway_params parameter db_type with the value 'servicenow', and supply the directory_name and file_name parameters along with username/password type credentials. OAuth 2.0 Authentication: you must supply the gateway_params parameter db_type with the value 'servicenow', and the directory_name, file_name, and token_uri parameters, along with OAuth type credentials. The directory_name parameter specifies the directory with the ServiceNow REST config file. You could create this directory as follows: create or replace directory servicenow_dir as 'SERVICENOW_DIR'; Obtain and download the ServiceNow REST config file to the specified directory. For example: exec DBMS_CLOUD.get_object('servicenow_dir_cred', 'https://objectstorage.<...>/servicenow.rest','SERVICENOW_DIR'); Set the file_name value to the name of the REST config file you downloaded, "servicenow.rest". Then you can use the ServiceNow REST config file with either basic authentication or OAuth2.0. Then you can use the ServiceNow REST config file with either basic authentication or OAuth2.0. See HETEROGENEOUS_CONNECTIVITY_INFO View for samples.
  sharepoint	When you use gateway_params parameter with db_type sharepoint, also specify values for auth_uri, scope, service_url, and token_uri. For db_type sharepoint, the credential you supply with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK must be an OAuth type credential using the params parameter with gcp_oauth2 values specified (client_id, client_secret, and refresh_token). See CREATE_CREDENTIAL Procedure for more information. Generate the refresh_token for tenant_id.sharepoint.com/.default offline_access. See the following for more information: Determine the scope and OAuth 2.0 authentication. Set values for gateway_params for db_type sharepoint: auth_uri: Set the auth_uri from Azure. See Obtain application client information and endpoints for more information. scope: Set the scope. See Connection option descriptions for more information. service_url: Set the service_url. See Service URL for more information. token_uri: Get the token_uri from Azure. See Obtain application client information and endpoints for more information.
  snowflake	When the db_type is SNOWFLAKE, the optional parameters: role, schema, and warehouse are valid. These values specify a different schema, role, or warehouse value, other than the default. For example: gateway_params => JSON_OBJECT( 'db_type' value 'snowflake', 'role' value 'ADMIN', 'schema' value 'PUBLIC', 'warehouse' value 'TEST' )
  youtube	To connect to Youtube and get data you must supply the gateway parameters directory_name and file_name. These parameters specify a model file (REST config file) that maps the JSON response to the relational model. The model file specifies the endpoints, table mapping, and HTTP response code for processing the JSON response. See Model file syntax and Example Model file for more information. When you use gateway_params parameter with db_type youtube, the credential you specify must be a Google OAuth credential (gcp_oauth2) See CREATE_CREDENTIAL Procedure for more information. OAuth 2.0 Authentication: you must supply the gateway_params parameter db_type with the value youtube, and the directory_name and file_name parameters, along with OAuth type credentials. The directory_name parameter specifies the directory with the ServiceNow REST config file. You could create this directory as follows: create or replace directory youtube_dir as 'YOUTUBE_DIR'; Obtain and download the ServiceNow REST config file to the specified directory. For example: exec DBMS_CLOUD.get_object('youtube_dir_cred', 'https://objectstorage.<...>/youtube.rest','YOUTUBE_DIR'); Set the file_name value to the name of the REST config file you downloaded, "youtube.rest". Then you can use the ServiceNow REST config file with OAuth2.0. See HETEROGENEOUS_CONNECTIVITY_INFO View for samples.

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

  In addition, when you create a Database Link in a schema other than the ADMIN schema, for example in a schema named adb_user, the adb_user schema must own the credential you use with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

• 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 AI Database for information on creating directories.

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

   SQL> ALTER SYSTEM SET GLOBAL_NAMES = FALSE;
  
   System altered.
  
   SQL> SHOW PARAMETER GLOBAL_NAMES
    NAME                     TYPE        VALUE
   ----------------------   ----------- -----------
    global_names             boolean     FALSE

• When the private_target parameter is TRUE, the hostname parameter specifies a private host inside the VCN.

Examples

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

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'AWS_REDSHIFT_LINK_CRED',
    username => 'NICK',
    password => 'password'
  );
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'AWSREDSHIFT_LINK',
          hostname => 'example.com',
          port => '5439',
          service_name => 'example_service_name',
          ssl_server_cert_dn => NULL,
          credential_name => 'AWS_REDSHIFT_LINK_CRED',
          gateway_params => JSON_OBJECT('db_type'  value 'awsredshift'));
END;
/

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'PRIVATE_ENDPOINT_CRED',
    username => 'db_user',
    password => 'password'
  );
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'PRIVATE_ENDPOINT_DB_LINK',
          hostname => 'exampleHostname',
          port => '1521',
          service_name => 'exampleServiceName',
          credential_name => 'PRIVATE_ENDPOINT_CRED',
          ssl_server_cert_dn => NULL,
          directory_name => NULL,
          private_target => TRUE);
END;
/

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'GOOGLE_BIGQUERY_CRED',
    params => JSON_OBJECT( 'gcp_oauth2' value JSON_OBJECT(
          'client_id' value 'client_id',
          'client_secret' value 'client_secret',
          'refresh_token' value 'refresh_token' )));

DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'GOOGLE_BIGQUERY_LINK',
          hostname => 'example.com',
          port => '443',
          service_name => 'example_service_name',
          credential_name => 'GOOGLE_BIGQUERY_CRED',
          gateway_params => JSON_OBJECT(
                     'db_type' value 'google_bigquery',
                     'project' value 'project_name1' ));
END;
/

The table name you specify when you use SELECT with Google BigQuery or Google Analytics must be in quotes. For example:

SELECT * FROM "sales"@GOOGLE_BIGQUERY_LINK

Use the rac_hostnames parameter with a target Oracle RAC database on a private endpoint.

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'DB_LINK_CRED1',
     username => 'adb_user',
     password => 'password');
      DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
     db_link_name => 'SALESLINK',
     rac_hostnames => '["sales1-svr1.example.adb.us-ashburn-1.oraclecloud.com",
                        "sales1-svr2.example.adb.us-ashburn-1.oraclecloud.com",
                        "sales1-svr3.example.adb.us-ashburn-1.oraclecloud.com"]',
     port => '1522',
     service_name => 'example_high.adb.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_CRED1',
     directory_name => 'EXAMPLE_WALLET_DIR',
     private_target => TRUE);
END;
/

DETACH_FILE_SYSTEM Procedure

This procedure detaches a file system from the database.

The DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure detaches a file system from your database. In addition to that, the DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure also removes the information about the file system from the DBA_CLOUD_FILE_SYSTEMS view.

Syntax

DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM(
    file_system_name         IN VARCHAR2
);

Parameters

Example

BEGIN
   DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM (
    file_system_name      => 'FSS'
  );
END;
/

Usage Notes

• To run this procedure, you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• You must have the WRITE privilege on the directory object in the database, to detach a file system from a directory using the DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure.

• The DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure can only detach a private File Storage Service in databases with Private Endpoints enabled.

  See OCI File Storage Service andConfigure Network Access with Private Endpoints for more information.

• The DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure looks up the Network File System hostname on the customer's virtual cloud network (VCN). The error "ORA-20000: Mounting NFS fails" is returned if the hostname specified in the location cannot be located.

DISABLE_APP_CONT Procedure

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

Syntax

DBMS_CLOUD_ADMIN.DISABLE_APP_CONT(
    service_name      IN VARCHAR2);

Parameters

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_type FROM DBA_SERVICES;

NAME                                                    FAILOVER_TYPE
------------------------------------------------------- --------------
nv123abc1_adb1_high.adb.oraclecloud.com

DISABLE_EXTERNAL_AUTHENTICATION Procedure

Disables user authentication with external authentication schemes for the database.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_EXTERNAL_AUTHENTICATION;

Exceptions

Example

BEGIN
   DBMS_CLOUD_ADMIN.DISABLE_EXTERNAL_AUTHENTICATION;
END;
/

PL/SQL procedure successfully completed.

DISABLE_FEATURE Procedure

This procedure disables the specified feature on the Autonomous AI Database instance.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
    feature_name    IN  VARCHAR2);

Parameters

Examples

BEGIN
DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
    feature_name => 'ORAMTS');
END;
/

BEGIN
DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
    feature_name => 'AUTO_DST_UPGRADE');
END;
/

BEGIN
DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
    feature_name => 'AUTO_DST_UPGRADE_EXCL_DATA');
END;
/

BEGIN
DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
    feature_name => 'OWM');
END;
/

BEGIN
DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
    feature_name => 'WORKLOAD_AUTO_REPLAY');
END;
/

Usage Notes

• To disable the OraMTS, AUTO_DST_UPGRADE, AUTO_DST_UPGRADE_EXCL_DATA, OWM, or WORKLOAD_AUTO_REPLAY features for your Autonomous AI Database instance, you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• When both AUTO_DST_UPGRADE and AUTO_DST_UPGRADE_EXCL_DATA are disabled, if new time zone versions are available the Autonomous AI Database instance does not upgrade to use the latest available time zone files.

• Query dba_cloud_config to verify that AUTO_DST_UPGRADE is disabled.

SELECT param_name, param_value FROM dba_cloud_config WHERE
       LOWER(param_name) = 'auto_dst_upgrade';

0 rows selected.

• Query dba_cloud_config to verify that AUTO_DST_UPGRADE_EXCL_DATA is disabled.

  SELECT param_name, param_value FROM dba_cloud_config WHERE
         LOWER(param_name) = 'auto_dst_upgrade_excl_data';
  
  0 rows selected.

DISABLE_OPERATOR_ACCESS Procedure

This procedure immediately revokes Cloud Operator access on the Autonomous AI Database instance.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_OPERATOR_ACCESS;

Example

BEGIN
    DBMS_CLOUD_ADMIN.DISABLE_OPERATOR_ACCESS;
END;
/

DISABLE_PRINCIPAL_AUTH Procedure

This procedure revokes principal based authentication for a specified provider on Autonomous AI Database and applies to the ADMIN user or to the specified user.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH(
       provider    IN VARCHAR2,
       username    IN VARCHAR2 DEFAULT 'ADMIN' );

Parameters

Usage Notes

• When the provider value is AZURE and the username is ADMIN, the procedure disables Azure service principal based authentication on Autonomous AI Database and deletes the Azure application on the Autonomous AI Database instance.

• When the provider value is AZURE and the username is a user other than the ADMIN user, the procedure revokes the privileges from the specified user. The ADMIN user and other users that are enabled to use the Azure service principal can continue to use ADMIN.AZURE$PA and the application that is created for the Autonomous AI Database instance remains on the instance.

Examples

BEGIN
     DBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH(
       provider => 'AZURE',
       username => 'SCOTT');
END;
/

BEGIN
     DBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH(
       provider => 'GCP');
END;
/

DISABLE_RESOURCE_PRINCIPAL Procedure

Disable resource principal credential for the database or for the specified schema.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_RESOURCE_PRINCIPAL(
   username         IN VARCHAR2);

Parameter

Exceptions

Usage Notes

• Resource principal is not available with refreshable clones.

• You must set up a dynamic group and policies for the dynamic group before you call DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL.

  See the following for more information on creating policies, creating a dynamic group, and creating rules:

  • Use Resource Principal to Access Oracle Cloud Infrastructure Resources

  • Managing Dynamic Groups

  • Managing Policies

• Verify that a resource principal credential is enabled by querying one of the views: DBA_CREDENTIALS or ALL_TAB_PRIVS.

  For example, as the ADMIN user query the view DBA_CREDENTIALS:

SELECT owner, credential_name FROM dba_credentials
        WHERE credential_name = 'OCI$RESOURCE_PRINCIPAL' AND owner = 'ADMIN';

OWNER  CREDENTIAL_NAME
-----  ----------------------
ADMIN  OCI$RESOURCE_PRINCIPAL

For example, as a non-ADMIN user query the view ALL_TAB_PRIVS:

SELECT grantee, table_name, grantor, FROM ALL_TAB_PRIVS
          WHERE  grantee = 'ADB_USER';

GRANTEE   TABLE_NAME GRANTOR
--------- -------------------------------------
ADB_USER  OCI$RESOURCE_PRINCIPAL ADMIN

Example

EXEC DBMS_CLOUD_ADMIN.DISABLE_RESOURCE_PRINCIPAL();

PL/SQL procedure successfully completed.

SQL> select owner, credential_name from dba_credentials where credential_name = 'OCI$RESOURCE_PRINCIPAL';

No rows selected.

DROP_DATABASE_LINK Procedure

This procedure drops a database link.

Syntax

DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(
        db_link_name      IN VARCHAR2,
        public_link       IN BOOLEAN DEFAULT);

Parameters

Examples

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

BEGIN
    DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(
        db_link_name => 'SALESLINK'
        public_link => TRUE );
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 AI Database.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_APP_CONT(
    service_name      IN VARCHAR2);

Parameters

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_type FROM DBA_SERVICES;

NAME                                                    FAILOVER_TYPE
------------------------------------------------------- -------------
nvthp2ht_adb1_high.adb.oraclecloud.com                 TRANSACTION

ENABLE_AWS_ARN Procedure

This procedure enables an Autonomous AI Database instance to use Amazon Resource Names (ARNs) to access AWS resources.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_AWS_ARN(
       username     IN VARCHAR2 DEFAULT NULL,
       grant_option IN BOOLEAN DEFAULT FALSE);

Parameters

Example

BEGIN
    DBMS_CLOUD_ADMIN.ENABLE_AWS_ARN(
        username => 'adb_user');
END;
/

Usage Note

• You must be the ADMIN user to run the procedure DBMS_CLOUD_ADMIN.ENABLE_AWS_ARN.

See Use Amazon Resource Names (ARNs) to Access AWS Resources for more information.

ENABLE_EXTERNAL_AUTHENTICATION Procedure

Enable users to login to the database with external authentication schemes.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
   type         IN VARCHAR2,
   force        IN BOOLEAN DEFAULT FALSE,
   params       IN CLOB DEFAULT NULL
);

Parameter

Exceptions

Usage Notes

• With type OCI_IAM, if the resource principal is not enabled on the Autonomous AI Database instance, this routine enables resource principal with DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL.

• This procedure sets the system parameters IDENTITY_PROVIDER_TYPE and IDENTITY_PROVIDER_CONFIG to required users to access the instance with Oracle Cloud Infrastructure Identity and Access Management authentication and authorization.

Examples

Enable OCI_IAM Authentication

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'OCI_IAM',
     force=> TRUE );
END;
/

PL/SQL procedure successfully completed.

Enable CMU Authentication for Microsoft Active Directory

You pass in a directory name that contains the CMU configuration files through params JSON argument.

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'CMU',
     force => TRUE,
     params => JSON_OBJECT('directory_name' value 'CMU_DIR'); // CMU_DIR directory object already exists
END;
/

PL/SQL procedure successfully completed.

You pass in a location URI pointing to an Object Storage location that contains CMU configuration files through params JSON argument.

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
       type     => 'CMU',
       params   => JSON_OBJECT('location_uri' value 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
                               'credential_name' value 'my_credential_name')
   );
END;
/

PL/SQL procedure successfully completed.

Enable Azure AD Authentication

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'AZURE_AD',
     force => TRUE,
     params   => JSON_OBJECT( 'tenant_id' VALUE '....',
                              'application_id' VALUE '...',
                              'application_id_uri' VALUE '.....' ));
END;
/

PL/SQL procedure successfully completed.

Enable Kerberos Authentication

You pass in a directory name that contains Kerberos configuration files through params JSON argument.

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'KERBEROS',
     force => TRUE,
     params => JSON_OBJECT('directory_name' value 'KERBEROS_DIR'); // KERBEROS_DIR directory object already exists
END;
/

PL/SQL procedure successfully completed.

You pass in a location URI pointing to an Object Storage location that contains Kerberos configuration files through params JSON argument:

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'KERBEROS',
     force => TRUE,
     params => JSON_OBJECT('location_uri' value 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
                           'credential_name' value 'my_credential_name');
END;
/

You pass in a service name with the kerberos_service_name in the params JSON argument:

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'KERBEROS',
     force => TRUE,
     params => JSON_OBJECT('directory_name' value 'KERBEROS_DIR', // KERBEROS_DIR directory object already exists
                           'kerberos_service_name' value 'oracle' ));
END;
/

After Kerberos is enabled on your Autonomous AI Database instance, use the following query to view the Kerberos service name:

SELECT SYS_CONTEXT('USERENV','KERBEROS_SERVICE_NAME') FROM DUAL;

ENABLE_FEATURE Procedure

This procedure enables the specified feature on the Autonomous AI Database instance.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_FEATURE(
    feature_name     IN VARCHAR2,
    params           IN  CLOB   DEFAULT NULL);

Parameters

Example to Enable JAVAVM Feature

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_FEATURE (
        feature_name => 'JAVAVM' );
END;
/

Example to Enable Auto DST Feature

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_FEATURE (
        feature_name => 'AUTO_DST_UPGRADE' );
END;
/

Example to Enable Auto DST EXCL Data Feature

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_FEATURE (
        feature_name => 'AUTO_DST_UPGRADE_EXCL_DATA' );
END;
/

Example to Enable OraMTS Feature

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_FEATURE(
        feature_name => 'ORAMTS',
        params       => JSON_OBJECT('location_uri' VALUE 'https://mymtsserver.mycorp.com')
   );
END;
/

Example to Enable OWM Feature

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_FEATURE(
        feature_name => 'OWM' );
END;
/

Example to Enable Workload Auto Replay Feature

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_FEATURE(
        feature_name => 'WORKLOAD_AUTO_REPLAY',
        params       => JSON_OBJECT('target_db_ocid' VALUE 'OCID1.autonomousdatabase.REGION..ID1', 'capture_duration' VALUE 120, 'capture_day' VALUE 'MONDAY', 'capture_time' VALUE '15:00'));
END;
/

A an error value of ORA-20000: Invalid argument for target_db_ocid could indicate that the OCID you supplied is not a refreshable clone. In this case, you need to supply an OCID with a value for a refreshable clone.

Usage Notes

• You must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN to run DBMS_CLOUD_ADMIN.ENABLE_FEATURE.

• After you run DBMS_CLOUD_ADMIN.ENABLE_FEATURE with feature_name value 'JAVAVM', you must restart the Autonomous AI Database instance to install Oracle Java.

  After you restart the Autonomous AI Database instance, the Java installation proceeds for some time after the database is opened.

  See Check Oracle Java Version for details on checking the status of the Java installation.

• After you run DBMS_CLOUD_ADMIN.ENABLE_FEATURE with feature_name value 'OWM', you must restart the Autonomous AI Database instance to enable Oracle Workspace Manager. Oracle.

• To enable AUTO_DST_UPGRADE, AUTO_DST_UPGRADE_EXCL_DATA, ORAMTS, JAVAVM, OWM, or WORKLOAD_AUTO_REPLAY features for your database, you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• By default, both AUTO_DST_UPGRADE and AUTO_DST_UPGRADE_EXCL_DATA are disabled. You can enable one or the other of these options, but not both.

• After you enable AUTO_DST_UPGRADE, the next time you restart, or stop and then start the Autonomous AI Database instance, the instance upgrades to use the latest available time zone files. After AUTO_DST_UPGRADE is enabled, when new time zone files are available, the instance continues to upgrade to the latest available version on every subsequent restart or stop and start, until the feature is disabled.

Query dba_cloud_config to verify that AUTO_DST_UPGRADE is enabled.

  SELECT param_name, param_value FROM dba_cloud_config WHERE
      LOWER(param_name) = 'auto_dst_upgrade';

    PARAM_NAME       PARAM_VALUE
    ---------------- --------------
    auto_dst_upgrade enabled

• After you enable AUTO_DST_UPGRADE_EXCL_DATA the Autonomous AI Database instance upgrades to use the latest available time zone files. After this feature is enabled, every subsequent maintenance window upgrades the instance to use the latest available time zone version. This feature assures that the time zone files are upgraded for the database (enabling AUTO_DST_UPGRADE_EXCL_DATA does not update any affected rows).

Query dba_cloud_config to verify that AUTO_DST_UPGRADE_EXCL_DATA is enabled.

SELECT param_name, param_value FROM dba_cloud_config WHERE
       LOWER(param_name) = 'auto_dst_upgrade_excl_data';

PARAM_NAME                    PARAM_VALUE

--------------------------    -----------
auto_dst_upgrade_excl_data    enabled

ENABLE_OPERATOR_ACCESS Procedure

Oracle Cloud Operations does not access your Autonomous AI Database instance and access is disallowed by default. When access is required to troubleshoot or mitigate an issue, you can allow a cloud operator access to the database schemas for a limited time.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_OPERATOR_ACCESS(
    auth_duration    IN  NUMBER  DEFAULT 1
);

Parameters

Examples

Example to enable Cloud Operator access for 12 hours duration on Autonomous AI Database:

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_OPERATOR_ACCESS(
       auth_duration => 12 );
END;
/

Example to enable Cloud Operator access for an unlimited duration on Autonomous AI Database:

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_OPERATOR_ACCESS(
       auth_duration => 'UNLIMITED' );
END;
/

Usage Notes

• ORA-20000: Operator access is already enabled indicates that operator access was already granted. In this case you have two options:

  • Wait for operator access to expire, and then grant operator access again with DBMS_CLOUD_ADMIN.ENABLE_OPERATOR_ACCESS

  • Explicitly disable operator access withDISABLE_OPERATOR_ACCESS Procedure.

• DBMS_CLOUD_ADMIN.ENABLE_OPERATOR_ACCESS allows access only to the Cloud Operator and does not enable access for any other user. All operations performed by the Cloud Operator are stored in the view DBA_OPERATOR_ACCESS. See View Oracle Cloud Infrastructure Operations Actions for more information.

• You allow a cloud operator to access the database schemas by running the procedure DBMS_CLOUD_ADMIN.ENABLE_OPERATOR_ACCESS. This means if you file a service request with Oracle Cloud Support and Oracle Cloud Operators need to access your database schemas, you must also enable operator access by runningDBMS_CLOUD_ADMIN.ENABLE_OPERATOR_ACCESS.

ENABLE_PRINCIPAL_AUTH Procedure

This procedure enables principal authentication on Autonomous AI Database for the specified provider and applies to the ADMIN user or the specified user.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(
       provider    IN VARCHAR2,
       username    IN VARCHAR2 DEFAULT 'ADMIN',
       params      IN CLOB DEFAULT NULL);

Parameters

Usage Notes

• When the provider parameter is AZURE, the params parameter must include the azure_tenantid in the following cases:

  • When DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH is called for the first time.

  • When DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH is called for the first time after DBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH is called with the provider parameter AZURE and the username ADMIN.

• When the provider parameter is AWS:

  • After you enable ARN on the Autonomous AI Database instance by running DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH, the credential named AWS$ARN is available to use with any DBMS_CLOUD API that takes a credential as the input.

Examples

BEGIN
     DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(
       provider => 'AZURE',
       username => 'SCOTT',
       params   => JSON_OBJECT('azure_tenantid' value 'azure_tenantid'));
END;
/

BEGIN
     DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(
     provider => 'GCP',
     username => 'SCOTT',
     params => JSON_OBJECT(
         'grant_option' value 'TRUE' ));
END;
/

BEGIN
     DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(
     provider => 'AWS',
     username => 'SCOTT',
     params => JSON_OBJECT(
         'aws_role_arn' value 'arn:aws:iam::123456:role/AWS_ROLE_ARN',
          'external_id_type' value 'TENANT_OCID'));
END;
/

ENABLE_RESOURCE_PRINCIPAL Procedure

Enable resource principal credential for the database or for the specified schema. This procedure creates the credential OCI$RESOURCE_PRINCIPAL.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(
   username         IN VARCHAR2,
   grant_option     IN BOOLEAN DEFAULT FALSE);

Parameters

Exceptions

Usage Notes

• You must call DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL with the ADMIN username or with no arguments before you call DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL with a username for a database user schema.

• You must set up a dynamic group and policies for the dynamic group before you call DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL.

  See the following for more information on policies, creating a dynamic group, and creating rules:

  • Use Resource Principal to Access Oracle Cloud Infrastructure Resources

  • Managing Dynamic Groups

  • Managing Policies

• Enabling the resource principal with DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL is one-time operation. You do not need to enable the resource principal again, unless you run DBMS_CLOUD_ADMIN.DISABLE_RESOURCE_PRINCIPAL to disable the resource principal.

• Resource principal is not available with refreshable clones.

Example

EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();

PL/SQL procedure successfully completed.

SQL> select owner, credential_name from dba_credentials where credential_name = 'OCI$RESOURCE_PRINCIPAL';

OWNER    CREDENTIAL_NAME
-------  ---------------
ADMIN    OCI$RESOURCE_PRINCIPAL

FINISH_WORKLOAD_CAPTURE Procedure

This procedure finishes the current workload capture, stops any subsequent workload capture requests to the database, and uploads the capture files to Object Storage.

Example

BEGIN
    DBMS_CLOUD_ADMIN.FINISH_WORKLOAD_CAPTURE
END;
/

Usage Notes

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• When you pass the duration parameter to START_WORKLOAD_CAPTURE, the capture finishes when it reaches the specified time. However, if you call FINISH_WORKLOAD_CAPTURE, this stops the workload capture (possibly before the time specified with the duration parameter).

  You can query the DBA_CAPTURE_REPLAY_STATUS view to check the finish workload status. See DBA_CAPTURE_REPLAY_STATUS View for more information.

Note:
You must subscribe to the Information event com.oraclecloud.databaseservice.autonomous.database.information to be notified about the completion of FINISH_WORKLOAD_CAPTURE as well as the Object Storage link to download the capture file. This PAR URL is contained in the captureDownloadURL field of the event and is valid for 7 days from the date of generation. See Information Events on Autonomous AI Database for more information.

PREPARE_REPLAY Procedure

The PREPARE_REPLAY procedure prepares the refreshable clone for a replay.

Parameters

Syntax

DBMS_CLOUD_ADMIN.PREPARE_REPLAY(
        capture_name IN VARCHAR2);

Example

BEGIN
    DBMS_CLOUD_ADMIN.PREPARE_REPLAY
      capture_name => 'cap_test1');
END;
/

This example prepares the refreshable clone to replay the workload indicated by the capture_name parameter, which involves bringing it up to the capture start time and then disconnecting it.

Usage Note

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

PURGE_FLASHBACK_ARCHIVE Procedure

This procedure enables ADMIN users to purge historical data from Flashback Data Archive. You can either purge all historical data from Flashback Data Archive flashback_archive or selective data based on timestamps or System Change Number.

Syntax

  DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE(
    scope      IN VARCHAR2,
    before_scn IN INTEGER DEFAULT NULL,
    before_ts  IN TIMESTAMP DEFAULT NULL);

Parameters

Example

BEGIN
	DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE(
           scope => 'ALL'); // Purge all historical data from Flashback Data Archive flashback_archive
END;
/

REPLAY_WORKLOAD Procedure

This procedure initiates a workload replay on your Autonomous AI Database instance. The overloaded form enables you to replay the capture files from an Autonomous AI Database instance, on-premises database, or other cloud service databases.

Syntax

DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD(
        capture_name                  IN VARCHAR2,
        replay_name                   IN VARCHAR2 DEFAULT NULL,
        capture_source_tenancy_ocid   IN VARCHAR2 DEFAULT NULL,
        capture_source_db_name        IN VARCHAR2 DEFAULT NULL);

DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD(
        location_uri      IN VARCHAR2,
        credential_name   IN VARCHAR2 DEFAULT NULL,
        synchronization   IN BOOLEAN  DEFAULT TRUE,
        process_capture   IN BOOLEAN  DEFAULT TRUE);

Parameters

Example to replay the workload from an on-premises database on an Autonomous AI Database instance:

BEGIN
  DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD(
        location_uri    => 'https://objectstorage.us-phoenix-
1.oraclecloud.com/n/namespace-string/b/bucketname/o',
        credential_name => 'CRED_TEST',
        synchronization => TRUE,
        process_capture => TRUE);
END;
/

When you run this example, it:

• Downloads the capture files from the Object Storage location specified in location_uri and processes the capture files based on the process_capture parameter value.

• Replays the captured workload based on the synchronization parameter value.

In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

See Navigate to Oracle Cloud Infrastructure Object Storage and Create Bucket for more information on Object Storage.

See Upload Files to Your Oracle Cloud Infrastructure Object Store Bucket for more information on uploading files to Object Storage.

The credential_name you use in this step is the credentials for the Object Store.

You don’t need to create a credential to access Oracle Cloud Infrastructure Object Store if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

Example to replay the workload from an Autonomous AI Database instance on another Autonomous AI Database

BEGIN
  DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD(
        capture_name => 'CAP_TEST1');
END;
/

When you run this example, it:

• Disconnects the current Autonomous AI Database instance.

• Downloads the capture files from the Object Storage.

• Replays the captured workload.

• Uploads replay report after a replay.

Usage Notes for Replaying the Workload from an On-Premises or Other Cloud Service Database on another Autonomous AI Database

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• Before you start replay, you should upload the cap and capfiles subdirectories, which contain the workload capture files, to the object storage location.

Usage Notes for Replaying the Workload from an Autonomous AI Database instance on another Autonomous AI Database

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• The replay files are automatically uploaded to the Object Store as a zip file.

• You can query the DBA_CAPTURE_REPLAY_STATUS view to check the workload replay status.

  See DBA_CAPTURE_REPLAY_STATUS View for more information.

Note: You must subscribe to the Information event com.oraclecloud.databaseservice.autonomous.database.information to be notified about the start and completion of the REPLAY_WORKLOAD as well as the Object Storage link to download the replay reports. This PAR URL is contained in the replayDownloadURL field of the event and is valid for 7 days from the date of generation. See Information Events on Autonomous AI Database for more information.

SET_FLASHBACK_ARCHIVE_RETENTION Procedure

This procedure allows ADMIN users to modify the retention period for Flashback Data Archive flashback_archive.

Syntax

DBMS_CLOUD_ADMIN.SET_FLASHBACK_ARCHIVE_RETENTION (
    retention_days INTEGER);

Parameters

Example

BEGIN
     DBMS_CLOUD_ADMIN.SET_FLASHBACK_ARCHIVE_RETENTION(
           retention_days => 90); // sets the retention time to 90 days
END;
/

START_WORKLOAD_CAPTURE Procedure

This procedure initiates a workload capture on your Autonomous AI Database instance.

Syntax

DBMS_CLOUD_ADMIN.START_WORKLOAD_CAPTURE(
        capture_name  IN VARCHAR2,
        duration      IN NUMBER   DEFAULT NULL);

Parameters

Example

BEGIN
  DBMS_CLOUD_ADMIN.START_WORKLOAD_CAPTURE(
        capture_name => 'test');
END;
/

Usage Notes

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• To measure the impacts of a system change on a workload, you must ensure that the capture and replay systems are in the same logical state.

• Before initiating a workload capture, you should consider provisioning a refreshable clone to ensure the same start point for the replay.

Note: You must subscribe to the Information event com.oraclecloud.databaseservice.autonomous.database.information to be notified at the start of START_WORKLOAD_CAPTURE. See Information Events on Autonomous AI Database for more information.

START_LIVE_WORKLOAD_REPLAY Procedure

This procedure initiates a live workload capture replay on your Autonomous AI Database instance.

Syntax

DBMS_CLOUD_ADMIN.START_LIVE_WORKLOAD_REPLAY(
        capture_replay_name  IN VARCHAR2,
        target_db_ocid       IN VARCHAR2,
        capture_duration     IN NUMBER DEFAULT NULL,
        reconnect_target     IN BOOLEAN DEFAULT TRUE
 );

Parameters

Example

BEGIN
  DBMS_CLOUD_ADMIN.START_LIVE_WORKLOAD_REPLAY(
        capture_replay_name => 'LiveReplayTest',
        target_db_ocid      => 'OCID1.autonomousdatabase.REGION..ID1',
        capture_duration    => 120,
        reconnect_target    => VALUE TRUE
 );
END;
/

Usage Notes

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• To measure the impacts of a system change on a workload, you must ensure that the capture and replay systems are in the same logical state.

• Before initiating a live workload capture replay, you should consider provisioning a refreshable clone to ensure the same start point for the replay.

PURGE_CONVERTED_STMTS

The DBMS_CLOUD_ADMIN.PURGE_CONVERTED_STMTS procedure deletes entries for converted SQL statements from the DBA_CONVERTED_STATEMENTS view when migrating from other Oracle databases to Autonomous AI Database.

Syntax

PROCEDURE DBMS_CLOUD_ADMIN.PURGE_CONVERTED_STMTS(
  retention_date IN TIMESTAMP DEFAULT NULL
);

Parameters

Example

BEGIN
  DBMS_CLOUD_ADMIN.PURGE_CONVERTED_STMTS(
    retention_date => SYSTIMESTAMP - INTERVAL '1' DAY
  );
END;
/

MASK_SQL_TEXT Procedure

The DBMS_CLOUD_ADMIN.MASK_SQL_TEXT procedure enables or disables SQL text masking.

Syntax

DBMS_CLOUD_ADMIN.MASK_SQL_TEXT(
   enable IN BOOLEAN,
   scope  IN VARCHAR2
);

Parameters

Example

BEGIN
  DBMS_CLOUD_ADMIN.MASK_SQL_TEXT(
    enable => FALSE,
    scope  => 'SYSTEM');
END;
/