Autonomous Database determines the accessibility of registered data sets as follows:

• The ADMIN user specifies a scope for a user that allows the user to register data sets based on the scope provided.

• A user who has been granted privileges to register data sets specifies a scope when they register a data set.

• Optionally, when you register a data set, a user who has been granted authorization privileges can specify that an authorization step required to access a data set. This authorization step is in addition to scope level access authorization.

ORA-20001: Share privileges are not enabled for current user or it is enabled but not for scope MY$REGION

The ADMIN user can revoke registration to disallow a user from registering data sets for remote access. The ADMIN user can also revoke privileges or database users to access registered data sets.

1. As the ADMIN user, revoke a user's privileges to register data sets.

   For example:

   EXEC DBMS_CLOUD_LINK_ADMIN.REVOKE_REGISTER('DB_USER1');

   This revokes the privileges to register data sets for the user, DB_USER1.

   Running DBMS_CLOUD_LINK_ADMIN.REVOKE_REGISTER does not affect data sets that are already registered. Use DBMS_CLOUD_LINK.UNREGISTER to remove access for a registered data set.

   See REVOKE_REGISTER Procedure and UNREGISTER Procedure for more information.

2. As the ADMIN user, revoke access to registered data sets.

   For example:

   EXEC DBMS_CLOUD_LINK_ADMIN.REVOKE_READ('LWARD');

   This revokes access to Cloud Links data sets for the user LWARD.

   See REVOKE_READ Procedure for more information.

You can register a table or view you own as a registered data set. When you want to remove or replace the data set, you need to unregister it. After you register a data set you can changes the values of a data set's attributes.

1. Obtain grant register privileges from the ADMIN user.

   See Grant Cloud Links Access for Database Users for more information.

2. Register one ore more data sets.

   For example, assuming there is a schema CLOUDLINK on your Autonomous Database instance, you can register two objects, SALES_VIEW_AGG and SALES_ALL and provide different scope parameters to determine how the objects are accessed.

   BEGIN
      DBMS_CLOUD_LINK.REGISTER(
       schema_name    => 'CLOUDLINK',
       schema_object  => 'SALES_VIEW_AGG',
       namespace      => 'REGIONAL_SALES', 
       name           => 'SALES_AGG',
       description    => 'Aggregated regional sales information.',
       scope          => 'MY$TENANCY',
       auth_required  =>  FALSE,
       data_set_owner =>  'amit@example.com' );
   END;
   /

   BEGIN
      DBMS_CLOUD_LINK.REGISTER(
       schema_name    => 'CLOUDLINK',
       schema_object  => 'SALES_ALL',
       namespace      => 'TRUSTED_COMPARTMENT', 
       name           => 'SALES',
       description    => 'Trusted Compartment, only accessible within my compartment. Early sales data.',
       scope          => 'MY$COMPARTMENT',
       auth_required  =>  FALSE,
       data_set_owner =>  'amit@example.com' );
   END;
   /

   The parameters are:

   • schema_name: is the schema name (the object owner).

   • schema_object: is the object's name. Valid objects are:

     • Tables (including Heap, External or Hybrid)
     • Views
     • Materialized Views
     • Cloud Tables (see Use Cloud Tables to Store Logging and Diagnostic Information for more information.)
     Note
     
     Other objects such as analytic views or synonyms are not supported.

   • namespace: is the namespace you provide as a name for access with Cloud Links (one part of the Cloud Link FQN).

     A NULL value specifies a system-generated namespace value, unique to the Autonomous Database instance.

   • name: is the name you provide for access with Cloud Links (one part of the Cloud Link FQN).

   • description: Specifies text to describe the data.

   • scope: Specifies the scope. You can use one of the variables: MY$REGION, MY$TENANCY, or MY$COMPARTMENT or specify one or more OCIDs.

     See Data Set Scope, Access Control, and Authorization for more information.

   • auth_required: A Boolean value that specifies if database level authorization is required for the data set, in addition to the scope access control. When this is set to TRUE, the data set enforces an additional authorization step. See Register a Data Set with Authorization Required for more information.

   • data_set_owner: Text value specifies information about the individual responsible for the data set or a contact for questions about the data set. For example, you could supply an email address for the data set owner.

   See REGISTER Procedure for more information.

   For this example, after the registration completes the scope for the two registered objects are different, based on the scope parameter you supplied with DBMS_CLOUD_LINK.REGISTER:

   • MY$TENANCY: Specifies tenancy level scope for REGIONAL_SALES.SALES_AGG.

   • MY$COMPARTMENT: Specifies the more restrictive compartment level scope within the tenancy for TRUSTED_COMPARTMENT.SALES.

BEGIN
   DBMS_CLOUD_LINK.UNREGISTER(
    namespace      => 'TRUSTED_COMPARTMENT', 
    name           => 'SALES');
END;
/

• Notes for Registering or Unregistering a Data Set
  Provides notes for registering a data set with DBMS_CLOUD_LINK.REGISTER and unregistering a data set with DBMS_CLOUD_LINK.UNREGISTER.

You can use Cloud Links in multiple regions, where the source region contains the data set's source database and one or more remote regions contain refreshable clones of the source database.

To use Cloud Links with a data set in a different region:

1. Create a cross-region Refreshable Clone of the source database in a remote region.

   See Create a Cross Tenancy or Cross-Region Refreshable Clone for information on adding a cross-region refreshable clone.

2. On the source database, register the data set.

   See Register or Unregister a Data Set for more information.

3. Refresh the refreshable clone.

   See Refresh a Refreshable Clone on Autonomous Database for more information.

4. On the remote Refreshable Clone register the data set using the same arguments you used to register the data set in the source region.

   For example, assuming there is a schema CLOUDLINK on your Autonomous Database instance, after you the register SALES_ALL on the source database, register SALES_ALL on the refreshable clone:

   BEGIN
      DBMS_CLOUD_LINK.REGISTER(
       schema_name    => 'CLOUDLINK',
       schema_object  => 'SALES_ALL',
       namespace      => 'TRUSTED_COMPARTMENT', 
       name           => 'SALES',
       description    => 'Trusted Compartment, only accessible within my compartment. Early sales data.',
       scope          => 'MY$COMPARTMENT',
       auth_required  =>  FALSE,
       data_set_owner =>  'amit@example.com' );
   END;
   /

   The parameters are:

   • schema_name: is the schema name (the object owner).

   • schema_object: is the object's name. Valid objects are:

     • Tables (including Heap, External or Hybrid)
     • Views
     • Materialized Views
     Note
     
     Other objects such as analytic views or synonyms are not supported.

   • namespace: is the namespace you provide as a name for access with Cloud Links (one part of the Cloud Link FQN).

     A NULL value specifies a system-generated namespace value, unique to the Autonomous Database instance.

   • name: is the name you provide for access with Cloud Links (one part of the Cloud Link FQN).

   • description: Specifies text to describe the data.

   • scope: Specifies the scope. You can use one of the variables: MY$REGION, MY$TENANCY, or MY$COMPARTMENT or specify one or more OCIDs.

     See Data Set Scope, Access Control, and Authorization for more information.

   • auth_required: A Boolean value that specifies if database level authorization is required for the data set, in addition to the scope access control. When this is set to TRUE, the data set enforces an additional authorization step. See Register a Data Set with Authorization Required for more information.

   • data_set_owner: Text value specifies information about the individual responsible for the data set or a contact for questions about the data set. For example, you could supply an email address for the data set owner.

   See REGISTER Procedure for more information.

   After the registration completes on the refreshable clone, the scope for the registered object is MY$COMPARTMENT: Specifies the more restrictive compartment level scope for my compartment within my tenancy for TRUSTED_COMPARTMENT.SALES.

• Notes for Registering or Unregistering a Data Set in a Remote Region
  Provides notes for registering a data set in a remote region.

Optionally, when you register a data set, in addition to the scope you can specify that database level authorization is required to access a data set.

1. Use DBMS_CLOUD_LINK.REGISTER to register a data with authorization required.

   Assuming there is a schema CLOUDLINK on your Autonomous Database instance and you register the object SALES_VIEW_AGG and set auth_required to TRUE, then in addition to defining the scope, you must preform additional steps to determine how the object is accessed.

   BEGIN
      DBMS_CLOUD_LINK.REGISTER(
       schema_name    => 'CLOUDLINK',
       schema_object  => 'SALES_VIEW_AGG',
       namespace      => 'REGIONAL_SALES', 
       name           => 'SALES_AGG',
       description    => 'Aggregated regional sales information.',
       scope          => 'MY$TENANCY',
       auth_required  =>  TRUE,
       data_set_owner =>  'amit@example.com' );
   END;
   /

   The parameters are:

   • schema_name: is the schema name (the object owner).

   • schema_object: is the object's name. Valid objects are:

     • Tables (including Heap, External or Hybrid)
     • Views
     • Materialized Views
     Note
     
     Other objects such as analytic views or synonyms are not supported.

   • namespace: is the namespace you provide as a name for access with Cloud Links (one part of the Cloud Link FQN).

     A NULL value specifies a system-generated namespace value, unique to the Autonomous Database instance.

   • name: is the name you provide for access with Cloud Links (one part of the Cloud Link FQN).

   • scope: Specifies the scope. You can use one of the variables: MY$REGION, MY$TENANCY, or MY$COMPARTMENT or specify one or more OCIDs.

     See Data Set Scope, Access Control, and Authorization for more information.

   • auth_required: A Boolean value that specifies if database level authorization is required for the data set, in addition to the scope access control. When this is set to TRUE, the data set enforces an additional authorization step.

   • data_set_owner: Text value specifies information about the individual responsible for the data set or a contact for questions about the data set. For example, you could supply an email address for the data set owner.

2. Obtain the database ID for the database you want to grant authorization to (to allow the database to access to the data set).

   On the system you want to grant access the data set:

   SELECT DBMS_CLOUD_LINK.GET_DATABASE_ID FROM DUAL:

3. Use the database ID you obtained to grant authorization to a specified data set.

   You can only grant authorization, and run DBMS_CLOUD_LINK.GRANT_AUTHORIZATION, as shown in this step, if you are authorized. The ADMIN grants authorization with DBMS_CLOUD_LINK_ADMIN.GRANT_AUTHORIZE.

   BEGIN
      DBMS_CLOUD_LINK.GRANT_AUTHORIZATION(
       database_id    => '120xxxxxxx8506029999',
       namespace      => 'TRUSTED_COMPARTMENT', 
       name           => 'SALES');
   END;
   /

   Perform these steps multiple times if you want to authorize additional databases.

BEGIN
   DBMS_CLOUD_LINK.REVOKE_AUTHORIZATION(
    database_id    => '120xxxxxxx8506029999',
    namespace      => 'TRUSTED_COMPARTMENT', 
    name           => 'SALES');
END;
/

Optionally, when you register a data set you can offload access to the data set to one or more Autonomous Database instances that are refreshable clones.

Use the optional offload_targets parameter with DBMS_CLOUD_LINK.REGISTER to specify that access is offloaded to refreshable clones. The source database for each refreshable clone is the Autonomous Database instance where you register the data set (data publisher).

The offload_targets value is a JSON document that defines one or more CLOUD_LINK_DATABASE_ID and OFFLOAD_TARGET key value pairs:

• CLOUD_LINK_DATABASE_ID is one of:

  • A database ID: This specifies a database ID for the data set consumer whose request is offloaded to the corresponding refreshable clone specified with the OFFLOAD_TARGET value.

    Obtain the database ID by running DBMS_CLOUD_LINK.GET_DATABASE_ID. See GET_DATABASE_ID Function for more information.

  • ANY: This specifies that any data set consumer's request is offloaded to the corresponding offload target. A consumer's data set request will be routed to the corresponding offload target.

    If you specify ANY without specifying database IDs, all data set requests from consumers are offloaded to the refreshable clone specified with the OFFLOAD_TARGET value.

    If you specify both database IDs and ANY, data set requests from consumers that do not match a database ID are offloaded to the refreshable clone specified with the OFFLOAD_TARGET value.

• OFFLOAD_TARGET is the OCID for an Autonomous Database instance that is a refreshable clone.

The following figure illustrates using offload targets.

When a data set consumer requests access to a data set that you register with offload_targets and the Autonomous Database instance's database ID matches the value specified in CLOUD_LINK_DATABASE_ID, access is offloaded to the refreshable clone identified with OFFLOAD_TARGET in the supplied JSON.

For example, the following shows a JSON sample with three OFFLOAD_TARGET/CLOUD_LINK_DATABASE_ID value pairs:

{
  "OFFLOAD_TARGETS": [
    {
      "CLOUD_LINK_DATABASE_ID": "34xxxxx69708978",
      "OFFLOAD_TARGET":
"ocid1.autonomousdatabase.oc1..xxxxx3pv6wkcr4jqae5f44n2b2m2yt2j6rx32uzr4h25vqstifsfabc"
    },
    {
      "CLOUD_LINK_DATABASE_ID": "34xxxxx89898978",
      "OFFLOAD_TARGET":
"ocid1.autonomousdatabase.oc1..xxxxx3pv6wkcr4jqae5f44n2b2m2yt2j6rx32uzr4h25vqstifsfdef"
    },
    {
      "CLOUD_LINK_DATABASE_ID": "34xxxxx4755680",
      "OFFLOAD_TARGET":
"ocid1.autonomousdatabase.oc1..xxxxx3pv6wkcr4jqae5f44n2b2m2yt2j6rx32uzr4h25vqstifsfghi"
    }
  ]
}

When a data set consumer requests access to a data set that you register with offload_targets that includes the ANY keyword, access is offloaded to the refreshable clone identified with OFFLOAD_TARGET in the supplied JSON (except for requests from consumers that have a matching database ID entry in the supplied JSON).

For example, the following shows a JSON sample with one explicit OFFLOAD_TARGET/CLOUD_LINK_DATABASE_ID value pair, and one ANY value with a corresponding OFFLOAD_TARGET:

{
  "OFFLOAD_TARGETS": [
    {
      "CLOUD_LINK_DATABASE_ID": "ANY",
      "OFFLOAD_TARGET":
"ocid1.autonomousdatabase.oc1..xxxxx3pv6wkcr4jqae5f44n2b2m2yt2j6rx32uzr4h25vqstifsfdef"
    },
    {
      "CLOUD_LINK_DATABASE_ID": "34xxxxx4755680",
      "OFFLOAD_TARGET":
"ocid1.autonomousdatabase.oc1..xxxxx3pv6wkcr4jqae5f44n2b2m2yt2j6rx32uzr4h25vqstifsfghi"
    }
  ]
}

To register a data set and specify offload targets, do the following:

1. Obtain the OCID for one or more refreshable clones where you want to offload data set access. Refreshable clone OCIDs are available on the Oracle Cloud Infrastructure Console on a refreshable clone.
   Note
   
   It can take up to 10 minutes after you create a refreshable clone for the refreshable clone to be visible as an offload target. This means you may need to wait up to 10 minutes after you create a refreshable clone for the refreshable clone to be available for Cloud Links offload registration.
2. Obtain the database ID for one or more Autonomous Database instances that you want to access the data set using data provided by the refreshable clone.

   On the system you want to access the data set from a refreshable clone, run the command:

   SELECT DBMS_CLOUD_LINK.GET_DATABASE_ID FROM DUAL:

3. Register a data set and specify the offload_targets parameter.

   For example, assuming there is a schema CLOUDLINK on your Autonomous Database instance, you can register SALES_VIEW_AGG and specify the refreshable clones that provide access to the data set:

   BEGIN
      DBMS_CLOUD_LINK.REGISTER(
       schema_name     => 'CLOUDLINK',
       schema_object   => 'SALES_VIEW_AGG',
       namespace       => 'REGIONAL_SALES', 
       name            => 'SALES_AGG',
       description     => 'Aggregated regional sales information.',
       scope           => 'MY$TENANCY',
       auth_required   =>  FALSE,
       data_set_owner  =>  'amit@example.com',
       offload_targets => '{
     "OFFLOAD_TARGETS": [
       {
         "CLOUD_LINK_DATABASE_ID": "34xxxx754755680",
         "OFFLOAD_TARGET":
   "ocid1.autonomousdatabase.oc1..xxxxxaaba3pv6wkcr4jqae5f44n2b2m2yt2j6rx32uzr4h25vqstifsfghi"
       }
     ]
   }');
   END;
   /

   The parameters are:

   • schema_name: is the schema name (the object owner).

   • schema_object: is the object's name. Valid objects are:

     • Tables (including Heap, External or Hybrid)
     • Views
     • Materialized Views
     Note
     
     Other objects such as analytic views or synonyms are not supported.

   • namespace: is the namespace you provide as a name for access with Cloud Links (one part of the Cloud Link FQN).

     A NULL value specifies a system-generated namespace value, unique to the Autonomous Database instance.

   • name: is the name you provide for access with Cloud Links (one part of the Cloud Link FQN).

   • scope: Specifies the scope. You can use one of the variables: MY$REGION, MY$TENANCY, or MY$COMPARTMENT or specify one or more OCIDs.

     See Data Set Scope, Access Control, and Authorization for more information.

   • auth_required: A Boolean value that specifies if database level authorization is required for the data set, in addition to the scope access control. When this is set to TRUE, the data set enforces an additional authorization step. See Register a Data Set with Authorization Required for more information.

   • data_set_owner: Text value specifies information about the individual responsible for the data set or a contact for questions about the data set. For example, you could supply an email address for the data set owner.

   • offload_targets: Specifies one or more Autonomous Database OCIDs of refreshable clones where access to data sets is offloaded, from the Autonomous Database where the data set is registered.

     For each data set consumer, one of the following can match to offload the request to a refreshable clone:

     • When there is matching the value of the specified cloud_link_database_id, that is the values matches the consumer's database ID, access is offloaded to the refreshable clone identified by the OCID specified in offload_target.

     • When the ANY keyword is specified, if there is not a matching the value of a specified cloud_link_database_id, access is offloaded to the refreshable clone identified with the ANY entry by the OCID specified in the corresponding offload_target.

   See the following for more information:

   • REGISTER Procedure

   • UPDATE_REGISTRATION Procedure

   • Use Refreshable Clones with Autonomous Database

After you register a data set you can update some data set attributes. You cannot update the schema name, schema object, namespace, or name attributes.

1. The user who registered a data set can update its attributes with DBMS_CLOUD_LINK.UPDATE_REGISTRATION.

   If you do not have privileges to update data set attributes, you need to obtain grant register privileges from the ADMIN user.

   See Grant Cloud Links Access for Database Users for more information.

2. Update one or more attributes for a data set.

   For example, use DBMS_CLOUD_LINK.UPDATE_REGISTRATION to update the description, scope, and auth_required attributes for the data set in the namespace REGIONAL_SALES with the name SALES_AGG:

   BEGIN
      DBMS_CLOUD_LINK.UPDATE_REGISTRATION(
       namespace      => 'REGIONAL_SALES', 
       name           => 'SALES_AGG',
       description    => 'Updated description for aggregated regional sales information.',
       scope          => 'MY$COMPARTMENT',
       auth_required  =>  TRUE );
   END;
   /

   The required parameters are:

   • namespace: is the data set's namespace you provided when you registered the data set.

   • name: is the data set's name you provided when you registered the data set.

   Following is a list of the optional parameters. If NULL is passed in for an optional parameter value, the attribute is not modified.

   • description: Specifies the updated text to describe the data.

   • scope: Specifies the scope. You can use one of the variables: MY$REGION, MY$TENANCY, or MY$COMPARTMENT or specify one or more OCIDs.

     See Data Set Scope, Access Control, and Authorization for more information.

   • auth_required: A Boolean value that specifies if database level authorization is required for the data set, in addition to the scope access control. When this is set to TRUE, the data set enforces an additional authorization step. See Register a Data Set with Authorization Required for more information.

   • data_set_owner: Text value specifies information about the individual responsible for the data set or a contact for questions about the data set. For example, you could supply an email address for the data set owner.

   • offload_targets: Specifies one or more Autonomous Database OCIDs of refreshable clones where access to data sets is offloaded, from the Autonomous Database where the data set is registered. See Register a Data Set with Offload Targets for Data Set Access for more information.

   You cannot update the schema_name or schema_object attributes.

   See UPDATE_REGISTRATION Procedure for more information.

You can set the service name mapping to use when Cloud Links consumers access data from a data set owner.

Cloud Links rely on database resources in the Autonomous Database instance that is the data set producer, or the resources of a refreshable clone, to access shared data. By default remote connectivity for consumers to access Cloud Links data uses the MEDIUM database service.

Use DBMS_CLOUD_LINK_ADMIN.ADD_SERVICE_MAPPING to set the database service mapping for a consumer. In this procedure you supply either a database ID or the keyword ANY to specify the consumer service mapping. For example, the following figure shows a mapping for Consumer A to the HIGH service, Consumer B to the MEDIUM service, Consumer C to the LOW service, and a mapping for ANY to the TP service, meaning all other consumers access Cloud Links using the TP service.

See Database Service Names for Autonomous Database for more information on the characteristics of database services.

Perform the following steps to set the database service to use for a Cloud Links consumer:

1. Obtain the database ID for the database that you want to set a service mapping.

   On the consumer database run GET_DATABASE_ID to obtain the consumer's database ID. For example:

   SELECT DBMS_CLOUD_LINK.GET_DATABASE_ID FROM DUAL:

   See GET_DATABASE_ID Function for more information.

2. On the data set owner's Autonomous Database instance, specify a service mapping.

   Perform this step on the data set owner's Autonomous Database instance (that is, on the producer database).

   BEGIN
      DBMS_CLOUD_LINK_ADMIN.ADD_SERVICE_MAPPING(
       database_id      => 'database_id', 
       service_name     => 'HIGH');
   END;
   /

   Where the database_id parameter value is the database ID you obtained in step 1.

   Specify the value ANY for the database_id to use the specified service_name with all consumer databases that do not have a service_name associated with their database_id. That is, any database_id whose service_name has not been set with DBMS_CLOUD_LINK_ADMIN.ADD_SERVICE_MAPPING.

   See ADD_SERVICE_MAPPING Procedure for more information.

   Only the ADMIN user and schemas with PDB_DBA role can run DBMS_CLOUD_LINK_ADMIN.ADD_SERVICE_MAPPING.

3. Verify the database IDs and service mapping by listing the Cloud Links service mappings.

   For example:

   SELECT * FROM  DBA_CLOUD_LINK_SERVICE_MAPPINGS;

   See DBA_CLOUD_LINK_SERVICE_MAPPINGS View for more information.

• Set Database Service Name Mapping for Cloud Links Consumers in Remote Region
  A data set that is registered in a source region can be accessed with Cloud Links from a remote region when you create cross-region Refreshable Clone in the remote region.

You can enable caching on a data set consumer for the results of a query or for a query fragment that accesses Cloud Link data.

1. In a query, specify the RESULT_CACHE hint with the SHELFLIFE option.

   For example:

   SELECT /*+ RESULT_CACHE (SHELFLIFE=120) */ * FROM FOREST.TREE_DATA@cloud$link;

   This RESULT_CACHE specifies a SHELFLIFE value of 120. This indicates that the result should be cached in memory for 120 seconds. After 120 seconds, the cached result is marked as invalid.

   The SHELFLIFE value must be a positive integer. The maximum SHELFLIFE value is 4294967295.

2. If the query result is large and does not fit in memory, use both the SHELFLIFE and the TEMP options to indicate that the result should be written to disk in the temporary tablespace.

   SELECT /*+ RESULT_CACHE (TEMP=true SHELFLIFE=360) */ * FROM FOREST.TREE_DATA@cloud$link;

You can share Cloud Links when a data set resides on a Read-Only Autonomous Database instance.

1. Change the database mode to Read/Write mode.
   See Change Autonomous Database Operation Mode to Read/Write Read-Only or Restricted for more information.
2. With the database in Read/Write mode, perform the steps to register a data set.
   1. Grant Cloud Links Access for Database Users
   2. Register or Unregister a Data Set
3. After you register one or more data sets, change the database to read-only mode.
   See Change Autonomous Database Operation Mode to Read/Write Read-Only or Restricted for more information.