Summary of DBMS_CLOUD_ADMIN Subprograms
This section
covers the DBMS_CLOUD_ADMIN
subprograms provided with Autonomous Database.
Subprogram | Description |
---|---|
This procedure attaches a file system in a directory on your database. |
|
This procedure cancels the current workload capture. |
|
This procedure creates a database link to a target database in the schema calling the API. You first need to upload the wallet ( |
|
This procedure detaches a file system from a directory on your database. |
|
This procedure disables database application continuity for the session associated with the specified service name in Autonomous Database. |
|
This procedure disables external authentication for the Autonomous Database instance. |
|
This procedure disables the specified feature on the Autonomous Database instance. |
|
This procedure revokes principal based authentication for the specified provider and applies to the ADMIN user or to the specified user. |
|
This procedure disables resource principal credential and creates the credential |
|
This procedure drops a database link. |
|
This procedure enables database application continuity for the session associated with the specified service name in Autonomous Database. |
|
This procedure enables a user to create AWS ARN credentials in Autonomous Database. |
|
This procedure enables a user to logon to Autonomous Database using the specified external authentication scheme. |
|
This procedure enables the specified feature on the Autonomous Database instance. |
|
This procedure enables principal authentication for the specified provider and applies to the ADMIN user or the specified user. |
|
This procedure enables resource principal credential and creates the credential |
|
This procedure stops the workload capture and uploads capture files to object storage. |
|
This procedure prepares replay for the refreshable clone. |
|
PURGE_FLASHBACK_ARCHIVE Procedure | This procedure purges historical data from the Flashback Data Archive. |
This procedure is overloaded. It initiates the workload replay. | |
This procedure enables ADMIN users to modify the retention period for Flashback Time Travel flashback_archive .
|
|
This procedure initiates a workload capture. |
- ATTACH_FILE_SYSTEM Procedure
This procedure attaches a file system in the database. - CANCEL_WORKLOAD_CAPTURE Procedure
This procedure cancels any ongoing workload capture on the database. - CREATE_DATABASE_LINK Procedure
This procedure creates a database link to a target database in the schema calling the API. The overloaded form enables you to create a database link with Oracle-managed heterogeneous connectivity to a supported non-Oracle database. - DETACH_FILE_SYSTEM Procedure
This procedure detaches a file system from the database. - DISABLE_APP_CONT Procedure
This procedure disables database application continuity for the session associated with the specified service name in Autonomous Database. - DISABLE_EXTERNAL_AUTHENTICATION Procedure
Disables user authentication with external authentication schemes for the database. - DISABLE_FEATURE Procedure
This procedure disables the specified feature on the Autonomous Database instance. - DISABLE_PRINCIPAL_AUTH Procedure
This procedure revokes principal based authentication for a specified provider on Autonomous Database and applies to the ADMIN user or to the specified user. - DISABLE_RESOURCE_PRINCIPAL Procedure
Disable resource principal credential for the database or for the specified schema. - 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. - ENABLE_AWS_ARN Procedure
This procedure enables an Autonomous Database instance to use Amazon Resource Names (ARNs) to access AWS resources. - ENABLE_EXTERNAL_AUTHENTICATION Procedure
Enable users to login to the database with external authentication schemes. - ENABLE_FEATURE Procedure
This procedure enables the specified feature on the Autonomous Database instance. - ENABLE_PRINCIPAL_AUTH Procedure
This procedure enables principal authentication on Autonomous Database for the specified provider and applies to the ADMIN user or the specified user. - ENABLE_RESOURCE_PRINCIPAL Procedure
Enable resource principal credential for the database or for the specified schema. This procedure creates the credentialOCI$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. - PREPARE_REPLAY Procedure
ThePREPARE_REPLAY
procedure prepares the refreshable clone for a replay. - 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 Archiveflashback_archive
or selective data based on timestamps or System Change Number. - REPLAY_WORKLOAD Procedure
This procedure initiates a workload replay on your Autonomous Database instance. The overloaded form enables you to replay the capture files from an Autonomous Database instance, on-premises database, or other cloud service databases. - SET_FLASHBACK_ARCHIVE_RETENTION Procedure
This procedure allows ADMIN users to modify the retention period for Flashback Data Archiveflashback_archive
. - START_WORKLOAD_CAPTURE Procedure
This procedure initiates a workload capture on your Autonomous Database instance.
Parent topic: DBMS_CLOUD_ADMIN Package
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
);
Parameters
Parameter | Description |
---|---|
|
Specifies the name of the file system. This parameter is mandatory. |
|
Specifies the location of the file system. The value you supply with For example:
This parameter is mandatory. |
|
Specifies the directory name for the attached file system. The directory must exist. This parameter is mandatory. |
|
Provides a description of the task. This parameter is mandatory. |
Example:
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;
/
Usage Notes
-
To run this procedure you must be logged in as the ADMIN user or have the
EXECUTE
privilege onDBMS_CLOUD_ADMIN
. -
You must have
WRITE
privilege on the directory object in the database to attach a file system usingDBMS_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 and Configuring 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 NFS Version 3 to share
- If you attach to non-Oracle Cloud Infrastructure File Storage systems, the procedure supports NFS Version 2 and NFS Version 3
-
DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM
does not support NFS Version 4
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
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 onDBMS_CLOUD_ADMIN
.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
CREATE_DATABASE_LINK Procedure
This procedure creates a database link to a target database in the schema calling the API. The overloaded form enables you to create a database link with Oracle-managed heterogeneous connectivity to a supported non-Oracle 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);
Parameters
Parameter | Description |
---|---|
|
The name of the database link to create. |
|
The hostname for the target database. When you specify a connection with Oracle-managed heterogeneous
connectivity by supplying
the
|
port |
Specifies the port for the connections to the target database. When you specify a connection with Oracle-managed heterogeneous
connectivity using the
See Oracle-Managed Heterogeneous Connectivity Database Types and Ports for more information. |
|
The
When you specify a connection with Oracle-managed heterogeneous
connectivity using the |
ssl_server_cert_dn |
The DN value found in the server certificate. Oracle-managed heterogeneous
connectivity is preconfigured with a wallet that
contains most of the common trusted root and intermediate SSL
certificates. The T o connect to an Oracle Database on a private endpoint without a wallet,
specify a
|
credential_name |
The name of a stored credential created with |
directory_name |
The directory for the stored Oracle-managed heterogeneous connectivity is preconfigured with a wallet that contains most of the common trusted root and intermediate SSL certificates. The T o connect to an Oracle Database with TCP, specify a
|
gateway_link |
Indicates if the database link is created to another Oracle Database or to an Oracle Database Gateway. If If When The default value for this parameter is
|
public_link |
Indicates if the database link is created as a public database link. To run The default value for this parameter is
|
private_target |
When a database link accesses a hostname that needs to be resolved in a VCN
DNS server, specify the When The default value for this parameter is
|
|
Specify the target database type for Oracle-managed heterogeneous
connectivity to connect to non-Oracle databases. The
Specify the parameter with the
gateway_params => json_object('db_type'
value 'AWSREDSHIFT') See Oracle-Managed Heterogeneous Connectivity Database Types and Ports for required When When |
Usage Notes
-
When you specify the
gateway_params
parameter, for somedb_type
values additionalgateway_params
parameters are supported:GOOGLE_BIGQUERY
When
db_type
isGOOGLE_BIGQUERY
, the parameterproject
is valid. This parameter specifies the project name onGOOGLE_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
HIVE
When
db_type
isHIVE
, the parameterhttp_path
is valid. This parameter specifies the HttpPath value, if required, to connect to the Hive instance.SALESFORCE
When the
db_type
isSALESFORCE
, the parameter:security_token
is valid. A security token is a case-sensitive alphanumeric code. Supplying asecurity_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.
SNOWFLAKE
When the
db_type
isSNOWFLAKE
, the parameters:role
,schema
, andwarehouse
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' )
-
When you use the
private_target
parameter, note that database links from an Autonomous Database to a database service that is on a private endpoint are only supported in commercial regions and US Government regions.This feature is enabled by default in all commercial regions.
This feature is enabled by default in US Government regions for newly provisioned databases.
For existing US Government databases on a private endpoint, if you want to create database links from an Autonomous Database to a target in a US Government region, please file a Service Request at Oracle Cloud Support and request to enable the private endpoint in government regions database linking feature.
US Government regions include the following:
- When connecting to a non-Oracle database, database linking is only supported if the target database is accessible through a public IP or a public hostname. See Create Database Links to Non-Oracle Databases for more information.
-
To run
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK
with a user other than ADMIN you need to grantEXECUTE
andCREATE DATABASE LINK
privileges to that user. For example, run the following command as ADMIN to grant privileges toadb_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 onecwallet.sso
at a time to the directory you choose for wallet files. This means with acwallet.sso
in a directory, you can only create database links to the databases for which the wallet in that directory is valid. To use multiplecwallet.sso
files with database links you need to create additional directories and put eachcwallet.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
toFALSE
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
-
When the
private_target
parameter isTRUE
, thehostname
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',
credential_name => 'AWS_REDSHIFT_LINK_CRED',
gateway_params => JSON_OBJECT('db_type' value 'AWSREDSHIFT'),
ssl_server_cert_dn => NULL);
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 must be in quotes. For example:
SELECT * FROM "sales"@GOOGLE_BIGQUERY_LINK
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
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
Parameter | Description |
---|---|
|
Specifies the name of the file system. This parameter is mandatory. |
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 onDBMS_CLOUD_ADMIN
. -
You must have the
WRITE
privilege on the directory object in the database, to detach a file system from a directory using theDBMS_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 and Configuring 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.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
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 |
---|---|
|
The To find service names:
|
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
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
DISABLE_EXTERNAL_AUTHENTICATION Procedure
Disables user authentication with external authentication schemes for the database.
Syntax
DBMS_CLOUD_ADMIN.DISABLE_EXTERNAL_AUTHENTICATION
;
Exceptions
Exception | Error | Description |
---|---|---|
invalid_ext_auth |
ORA-20004 |
See the accompanying message for a detailed explanation. |
Example
BEGIN
DBMS_CLOUD_ADMIN.DISABLE_EXTERNAL_AUTHENTICATION
;
END;
/
PL/SQL procedure successfully completed.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
DISABLE_FEATURE Procedure
This procedure disables the specified feature on the Autonomous Database instance.
Syntax
DBMS_CLOUD_ADMIN.DISABLE_FEATURE
(
feature_name IN VARCHAR2);
Parameters
Parameter | Description |
---|---|
|
Specifies the feature type to be disabled. Supported values are:
This parameter is mandatory. |
Example
BEGIN
DBMS_CLOUD_ADMIN.DISABLE_FEATURE
(
feature_name => 'ORAMTS');
END;
/
Example
BEGIN
DBMS_CLOUD_ADMIN.DISABLE_FEATURE
(
feature_name => 'AUTO_DST_UPGRADE');
END;
/
Example
BEGIN
DBMS_CLOUD_ADMIN.DISABLE_FEATURE
(
feature_name => 'OWM');
END;
/
Usage Notes
-
To disable the
OraMTS
,AUTO_DST_UPGRADE
, orOWM
features for your Autonomous Database instance, you must be logged in as the ADMIN user or have theEXECUTE
privilege onDBMS_CLOUD_ADMIN
. -
When the
AUTO_DST_UPGRADE
feature is disabled, if new timezone versions are available the Autonomous Database instance does not upgrade to use the latest available version. -
Query
dba_cloud_config
to verify thatAUTO_DST_UPGRADE
is disabled.SELECT param_name, param_value FROM dba_cloud_config WHERE LOWER(param_name) = 'auto_dst_upgrade'; 0 rows selected.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
DISABLE_PRINCIPAL_AUTH Procedure
This procedure revokes principal based authentication for a specified provider on Autonomous 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
Parameter | Description |
---|---|
|
Specifies the type of provider. Valid values:
|
|
Specifier the user to disable principal based authentication for. A null value is valid for the
|
Usage Notes
-
When the provider value is
AZURE
and theusername
isADMIN
, the procedure disables Azure service principal based authentication on Autonomous Database and deletes the Azure application on the Autonomous Database instance. -
When the provider value is
AZURE
and theusername
is a user other than theADMIN
user, the procedure revokes the privileges from the specified user. TheADMIN
user and other users that are enabled to use the Azure service principal can continue to useADMIN.AZURE$PA
and the application that is created for the Autonomous 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;
/
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
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
Parameter | Description |
---|---|
|
Specifies an optional user name. The name of the database schema to remove resource principal access. If you do not supply a |
Exceptions
Exception | Error | Description |
---|---|---|
resource principal is already
disabled |
ORA-20031 |
If you attempt to disable the resource principal when it is already disabled. |
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:
-
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.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
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
Parameter | Description |
---|---|
|
The name of the database link to drop. |
|
To run The default value for this parameter is
|
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 thedata_pump_dir
directory or from the user defined directory where the wallet file was uploaded.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
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 |
---|---|
|
The To find service names:
|
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
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
ENABLE_AWS_ARN Procedure
This procedure enables an Autonomous 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
Parameter | Description |
---|---|
|
Name of the user to enable to use Amazon Resource Names (ARNs). A null value is valid for the |
|
When |
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.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
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
Parameter | Description |
---|---|
|
Specifies the external authentication type. Valid values: or .
|
|
(Optional) Override a currently enabled external
authentication scheme. Valid values are The default value is |
params |
A JSON string that provides additional parameters for the external authentication.
|
Exceptions
Exception | Error | Description |
---|---|---|
invalid_ext_auth |
ORA-20004 |
See the accompanying message for a detailed explanation. |
Usage Notes
-
With
type
OCI_IAM
, if the resource principal is not enabled on the Autonomous Database instance, this routine enables resource principal withDBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL
. -
This procedure sets the system parameters
IDENTITY_PROVIDER_TYPE
andIDENTITY_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;
/
PL/SQL procedure successfully completed.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
ENABLE_FEATURE Procedure
This procedure enables the specified feature on the Autonomous Database instance.
Syntax
DBMS_CLOUD_ADMIN.ENABLE_FEATURE
(
feature_name IN VARCHAR2,
params IN CLOB DEFAULT NULL);
Parameters
Parameter | Description |
---|---|
|
Name of the feature to enable. The supported values are:
This parameter is mandatory. |
params |
A JSON string that provides additional parameters for some features. The
params parameter is:
|
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 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;
/
Usage Notes
-
After you run
DBMS_CLOUD_ADMIN.ENABLE_FEATURE
withfeature_name
value'JAVAVM'
, you must restart the Autonomous Database instance to install Oracle Java. -
To enable
AUTO_DST_UPGRADE
,ORAMTS
,JAVAVM
, orOWM
features for your database, you must be logged in as the ADMIN user or have theEXECUTE
privilege onDBMS_CLOUD_ADMIN
. -
After you enable
AUTO_DST_UPGRADE
, the next time you restart, or stop and then start the Autonomous Database instance, the instance upgrades to use the latest available timezone version. When isAUTO_DST_UPGRADE
enabled, when new timezone versions 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 thatAUTO_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 enable
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
ENABLE_PRINCIPAL_AUTH Procedure
This procedure enables principal authentication on Autonomous 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
Parameter | Description |
---|---|
|
Specifies the type of provider. Valid values:
|
|
Name of the user who has principal authentication usage enabled. A null value is valid for the
|
|
Specifies the configuration parameters. When the When the
When the
|
Usage Note
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 afterDBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH
is called with theprovider
parameterAZURE
and theusername
ADMIN
.
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');
END;
/
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
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);
Parameter
Parameter | Description |
---|---|
|
Specifies an optional user name. The name of the database schema to be granted resource principal access. If you do not supply a |
|
When |
Exceptions
Exception | Error | Description |
---|---|---|
resource principal is already
enabled |
ORA-20031 |
If you attempt to enable the resource principal when it is already enabled. |
Usage Notes
-
You must call
DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL
with the ADMINusername
or with no arguments before you callDBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL
with ausername
for a database user schema. -
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 policies, creating a dynamic group, and creating rules:
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
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
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 onDBMS_CLOUD_ADMIN
. -
When you pass the
duration
parameter toSTART_WORKLOAD_CAPTURE
, the capture finishes when it reaches the specified time. However, if you callFINISH_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 Database for more information.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
PREPARE_REPLAY Procedure
The PREPARE_REPLAY
procedure prepares the refreshable clone for a replay.
Parameters
Parameter | Description |
---|---|
capture_name |
Specifies the name of the workload capture.
This parameter is mandatory. |
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 onDBMS_CLOUD_ADMIN
.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
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);
Parameter | Description |
---|---|
scope |
This specifies the scope to remove data from the flashback data archive.
|
before_scn |
This specifies the system change number before which all the data is removed from the flashback archive. |
before_timestamp |
This specifies the timestamp before which all the data is removed from the flashback archive. |
Example
BEGIN
DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE(
scope => 'ALL'); // Purge all historical data from Flashback Data Archive flashback_archive
END;
/
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
REPLAY_WORKLOAD Procedure
This procedure initiates a workload replay on your Autonomous Database instance. The overloaded form enables you to replay the capture files from an Autonomous 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
Parameter | Description |
---|---|
CAPTURE_NAME |
Specifies the name of the workload capture.
This parameter is mandatory. |
REPLAY_NAME |
Specifies the replay name.
If you do not supply a |
CAPTURE_SOURCE_TENANCY_OCID |
Specifies the source tenancy OCID of the workload capture.
If you do not supply a This parameter is only mandatory when running the workload capture in a full clone. |
CAPTURE_SOURCE_DB_NAME |
Specifies the source database name of the workload capture
If you do not supply a This parameter is only mandatory when running the workload capture in a full clone. |
LOCATION_URI |
Specifies URI that points to an Object Storage location that contains the captured files.
This parameter is mandatory. |
CREDENTIAL_NAME |
Specifies the credential to access the object storage bucket.
If you do not supply a |
SYNCHRONIZATION |
Specifies the synchronization method used during workload replay.
If you do not supply a |
PROCESS_CAPTURE |
Specifies whether or not you need to specify process_capture value. It can be set to FALSE only when you replay the same workload on the target database repeatedly.
If you do not supply a |
Example to replay the workload from an on-premises database on an Autonomous 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 theprocess_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 Database instance on another Autonomous Database:
BEGIN
DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD
(
capture_name => 'CAP_TEST1');
END;
/
When you run this example, it:
-
Disconnects the current Autonomous 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 Database
-
To run this procedure you must be logged in as the ADMIN user or have the
EXECUTE
privilege onDBMS_CLOUD_ADMIN
. -
Before you start replay, you should upload the
cap
andcapfiles
subdirectories, which contain the workload capture files, to the object storage location.
Usage Notes for Replaying the Workload from an Autonomous Database instance on another Autonomous Database
-
To run this procedure you must be logged in as the ADMIN user or have the
EXECUTE
privilege onDBMS_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 Database for more information.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
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);
Parameter | Description |
---|---|
retention_days |
This specifies the length of time in days that the archived data should be retained for. The value of retention_days must be greater than 0.
|
Example
BEGIN
DBMS_CLOUD_ADMIN.SET_FLASHBACK_ARCHIVE_RETENTION
(
retention_days => 90); // sets the retention time to 90 days
END;
/
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
START_WORKLOAD_CAPTURE Procedure
This procedure initiates a workload capture on your Autonomous Database instance.
Syntax
DBMS_CLOUD_ADMIN.START_WORKLOAD_CAPTURE
(
capture_name IN VARCHAR2,
duration IN NUMBER DEFAULT NULL);
Parameters
Parameter | Description |
---|---|
capture_name |
Specifies the name of the workload capture.
This parameter is mandatory. |
duration |
Specifies the duration in minutes for which you want to run the workload capture.
|
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 onDBMS_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 Database for more information.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms