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. There are options to create a database link to another Autonomous Database instance, to an Oracle Database that is not an Autonomous Database, or to a non-Oracle Database using Oracle-managed heterogeneous connectivity. |
|
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. |
|
Immediately revokes Cloud Operator access on the Autonomous Database 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 credentials for the database or for the specified schema. With a user name specified, other than ADMIN, the procedure revokes the specified schema access to the resource principal 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. |
|
Grants the Cloud Operator access to an Autonomous Database instance for a specified number of hours. |
|
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. - 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_OPERATOR_ACCESS Procedure
This procedure immediately revokes Cloud Operator access 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_OPERATOR_ACCESS Procedure
Oracle Cloud Operations does not access your Autonomous 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. - 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 DEFAULT NULL,
params IN CLOB DEFAULT NULL
);
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. |
|
(Optional) Provides a description of the task. |
|
A JSON string that provides an additional parameter for the file system.
|
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 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 Configure 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 thenDBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM
(using theparams
parameter withnfs_version
set to 4). This attaches NFS with the matching protocol so that Autonomous 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"
.
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
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 Database on a private endpoint to a target Oracle RAC database. In this case, you use therac_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
Parameter | Description |
---|---|
|
The name of the database link to create. |
|
The hostname for the target database. Specifying When you specify a connection with Oracle-managed heterogeneous
connectivity by supplying the
Use this parameter or |
|
Specifies hostnames for the target Oracle RAC database. The value is a JSON
array that specifies one or more individual host names for the
nodes of the target Oracle RAC database. Multiple host names can
be passed in JSON, separated by a "
When the target is an Oracle RAC
database, use the When you specify a list of host
names in the Use this parameter or Specifying |
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 Public Endpoint Link to an Autonomous Database Target without a Wallet: To connect to an Autonomous Database target on a public endpoint without a wallet (TLS):
Private Endpoint Link without a Wallet: To connect to an Oracle Database on a private endpoint without a wallet:
|
credential_name |
The name of a stored credential created with |
directory_name |
The directory for the Oracle-managed heterogeneous
connectivity is preconfigured with a wallet that
contains most of the common trusted root and intermediate SSL
certificates. The Public Endpoint Link to an Autonomous Database Target without a Wallet: To connect to an Autonomous Database on a public endpoint without a wallet (TLS):
In addition, to connect to an Autonomous Database with TCP, the
Private Endpoint Link without a Wallet: To connect to a target Oracle Database on a private endpoint without a wallet:
|
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 parameter with the
For example:
See Oracle-Managed Heterogeneous Connectivity Database Types and Ports for required When When
For a database on a private endpoint, the
For example:
|
Usage Notes
-
When you specify the
gateway_params
parameter, for somedb_type
values, additionalgateway_params
parameters are supported:db_type
Additional gateway_params
Valuesazure
When the
db_type
value isazure
there are two optional parameters,auth_method
anddomain
to support NTLM/AD authentication.When NTLMv2 is configured, set
auth_method=10
and setdomain
to the Windows domain value.google_analytics
When the
db_type
isgoogle_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
isgoogle_bigquery
, the credential you specify must be a Google OAuth credential (gcp_oauth2)
See CREATE_CREDENTIAL Procedure for more information.When
db_type
isgoogle_bigquery
, the parameterproject
is valid. This parameter specifies the project name forgoogle_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.
servicenow
To connect to ServiceNow and get data you must supply the gateway parameters
directory_name
andfile_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 withdb_type
servicenow
, there are two supported options:-
Basic Authentication: you must supply the
gateway_params
parameterdb_type
with the value'servicenow'
, and supply thedirectory_name
andfile_name
parameters along with username/password type credentials. -
OAuth 2.0 Authentication: you must supply the
gateway_params
parameterdb_type
with the value'servicenow'
, and thedirectory_name
,file_name
, andtoken_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 withdb_type
sharepoint
, also specify values forauth_uri
,scope
,service_url
, andtoken_uri
.For
db_type
sharepoint
, the credential you supply withDBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK
must be an OAuth type credential using theparams
parameter withgcp_oauth2
values specified (client_id
,client_secret
, andrefresh_token
). See CREATE_CREDENTIAL Procedure for more information. Generate therefresh_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
fordb_type
sharepoint
:-
auth_uri
: Set theauth_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 theservice_url
. See Service URL for more information. -
token_uri
: Get thetoken_uri
from Azure. See Obtain application client information and endpoints for more information.
snowflake
When the
db_type
isSNOWFLAKE
, the optional 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' )
youtube
To connect to Youtube and get data you must supply the gateway parameters
directory_name
andfile_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 withdb_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
parameterdb_type
with the valueyoutube
, and thedirectory_name
andfile_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 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;
In addition, when you create a Database Link in a schema other than the ADMIN schema, for example in a schema named
adb_user
, theadb_user
schema must own the credential you use withDBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK
. -
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',
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;
/
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 Configure 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. |
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
, orWORKLOAD_AUTO_REPLAY
features for your Autonomous Database instance, you must be logged in as the ADMIN user or have theEXECUTE
privilege onDBMS_CLOUD_ADMIN
. -
When both
AUTO_DST_UPGRADE
andAUTO_DST_UPGRADE_EXCL_DATA
are disabled, if new time zone versions are available the Autonomous Database instance does not upgrade to use the latest available time zone files. -
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.
-
Query
dba_cloud_config
to verify thatAUTO_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.
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
DISABLE_OPERATOR_ACCESS Procedure
This procedure immediately revokes Cloud Operator access on the Autonomous Database instance.
Syntax
DBMS_CLOUD_ADMIN.DISABLE_OPERATOR_ACCESS
;
Example
BEGIN
DBMS_CLOUD_ADMIN.DISABLE_OPERATOR_ACCESS
;
END;
/
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;
/
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 Database instance, use the following query to view the Kerberos service name:
SELECT SYS_CONTEXT('USERENV','KERBEROS_SERVICE_NAME') FROM DUAL;
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. For the
OraMTS feature the params parameter is:
For the
By default the workload capture starts when you enable
|
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 onDBMS_CLOUD_ADMIN
to runDBMS_CLOUD_ADMIN.ENABLE_FEATURE
. -
After you run
DBMS_CLOUD_ADMIN.ENABLE_FEATURE
withfeature_name
value'JAVAVM'
, you must restart the Autonomous Database instance to install Oracle Java.After you restart the Autonomous 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
withfeature_name
value'OWM'
, you must restart the Autonomous Database instance to enable Oracle Workspace Manager. Oracle. -
To enable
AUTO_DST_UPGRADE
,AUTO_DST_UPGRADE_EXCL_DATA
,ORAMTS
,JAVAVM
,OWM
, orWORKLOAD_AUTO_REPLAY
features for your database, you must be logged in as the ADMIN user or have theEXECUTE
privilege onDBMS_CLOUD_ADMIN
. -
By default, both
AUTO_DST_UPGRADE
andAUTO_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 Database instance, the instance upgrades to use the latest available time zone files. AfterAUTO_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 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 enabled
-
After you enable
AUTO_DST_UPGRADE_EXCL_DATA
the Autonomous 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 (enablingAUTO_DST_UPGRADE_EXCL_DATA
does not update any affected rows).Query
dba_cloud_config
to verify thatAUTO_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
Parent topic: Summary of DBMS_CLOUD_ADMIN Subprograms
ENABLE_OPERATOR_ACCESS Procedure
Oracle Cloud Operations does not access your Autonomous 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
Parameter | Description |
---|---|
|
Specifies the number of Hours for which Cloud Operator is granted access. Valid values: must be whole numbers in the range of 1 to 24. Default value: 1 |
Example
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_OPERATOR_ACCESS
(
auth_duration => 12 );
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 with DISABLE_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 viewDBA_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
.
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 When the
When the
|
Usage Notes
-
When the
provider
parameter isAZURE
, theparams
parameter must include theazure_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
.
-
-
When the
provider
parameter isAWS
:-
After you enable ARN on the Autonomous Database instance by running
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH
, the credential namedAWS$ARN
is available to use with anyDBMS_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;
/
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. -
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:
-
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 runDBMS_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
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.
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.
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.
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