Create Federated Tables using Table Hyperlinks by Defining Scope

You can create a Federated Table over an Autonomous AI Database Table Hyperlink. A Federated Table is an external table defined on Table Hyperlinks. It enables aggregation of data from multiple Autonomous AI Databases instances.

Although a federated table uses the same hyperlink mechanism as an external table, the creation workflow differs. For external tables, the Provider creates and shares Table Hyperlinks, and each Consumer uses those hyperlinks to define external tables. For federated tables, the Consumer initiates the creation of the table, and Table Hyperlinks are automatically created in the Provider database, provided the Consumer is within the scope defined by the Provider.

As a Provider, you can define scopes in your database to grant Consumers the privilege to automatically create Table Hyperlinks. The authorized Consumers within these scopes can create federated tables and query data from multiple source databases without manual link exchange.

The key benefits of defining scopes to create Table Hyperlinks are:

• Simplified Data Sharing – The consumers can now initiate creation of external table separately and can create table hyperlinks when they belong to an authorized scope.
• Enhanced Security - Eliminates insecure Table Hyperlink (URL) distribution methods.
• Data Federation - Consumers can aggregate data from multiple provider databases through federated tables.

The following sections outline the detailed workflow on how the Provider and Consumer Autonomous Databases create Federated Tables together by defining scopes in an example practical use case. This workflow and the associated code examples can be modified and implemented according to your requirements.

• Workflow to Create Federated Tables
  The following workflow has distinct responsibilities for the provider Autonomous AI Database and the consumer Autonomous AI Database.
• Troubleshooting Scenarios
  This section provides instructions on the types of failure that can occur and how to troubleshoot the issues.

Workflow to Create Federated Tables

The following workflow has distinct responsibilities for the provider Autonomous AI Database and the consumer Autonomous AI Database.

From the provider’s side, the first step is to define a creation scope that specifies which consumer Autonomous AI Databases are allowed to remotely create Table Hyperlinks in the provider. The scope can be set at schema or object level.

Next, the provider DBA controls who can manage scopes by calling DBMS_DATA_ACCESS_ADMIN.GRANT_REGISTER for selected users. These privileged users can use DBMS_DATA_ACCESS_SCOPE.REGISTER_CREATION_SCOPE, UPDATE_CREATION_SCOPE, and UNREGISTER_CREATION_SCOPE to register, change, or remove scopes for specific tables or schemas, and LIST_CREATION_SCOPES to review the current configuration. When a consumer later requests a hyperlink, the provider checks that the requesting consumer Autonomous AI Database matches the registered scope before allowing URL generation, ensuring that only entitled consumers can create hyperlinks into provider data.

From the consumer’s perspective, the workflow starts once the provider has defined scopes that include the consumer database. The consumer DBA grants specific users the privilege to create federated tables over provider data by calling DBMS_DATA_ACCESS_ADMIN.GRANT_READ.

A consumer can then call DBMS_DATA_ACCESS.CREATE_FEDERATED_TABLE to create Table Hyperlinks in the provider’s database on behalf of the consumer and the resulting federated table in the consumer can then query the producer data as if it were a local external table.

When the federated access is no longer required, the consumer cleans up by calling DBMS_DATA_ACCESS.DROP_FEDERATED_TABLE, which removes the federated table object in the consumer while leaving the provider’s scopes intact.

Consider an organization using Oracle Autonomous AI Database instances where a central provider Autonomous AI Database holds shared master data, and departmental consumer Autonomous AI Databases need analytics access without data duplication or support tickets. The business analysts in the Analytics department require self-service creation of external tables over provider data to run reports faster, while maintaining governance through provider-defined scopes. They finalized on the following requirements:

1. The ADMIN (Provider) grants scope registration privilege to data owner using the DBMS_DATA_ACCESS_ADMIN.GRANT_REGISTER procedure.

   BEGIN
   DBMS_DATA_ACCESS_ADMIN.GRANT_REGISTER(
   username => 'DATA_OWNER',
   scope => 'MY$COMPARTMENT'
   );
   END;
   /

2. As ADMIN, grant execute privileges to the data owner (DATA_OWNER) user.

   grant execute on DBMS_DATA_ACCESS_SCOPE to DATA_OWNER;

3. The Data owner registers creation scope on shared schema or object in the provider Autonomous AI Database using the DBMS_DATA_ACCESS_SCOPE.REGISTER_CREATION_SCOPE procedure. This authorizes consumer Autonomous AI Databases in the same compartment to create table hyperlinks remotely.

   BEGIN
   DBMS_DATA_ACCESS_SCOPE.REGISTER_CREATION_SCOPE(
   schema_name => 'DATA_OWNER',
   schema_object_name => 'SALES_DATA',
   scope => 'MY$COMPARTMENT'
   );
   END;
   /

4. Consumer ADMIN grants read privilege to business analyst using the DBMS_DATA_ACCESS_ADMIN.GRANT_READ procedure.

   This ensures the analyst can initiate federated table creation.​

5. Consumer ADMIN grants execution privileges to the bi_analyst user.

   grant execute on DBMS_DATA_ACCESS to bi_analyst;

6. Business analyst creates federated external table in consumer Autonomous AI Database using the DBMS_DATA_ACCESS.CREATE_FEDERATED_TABLE procedure. This generates the Table hyperlink automatically if scope matches where no provider intervention is required.

   BEGIN
   DBMS_DATA_ACCESS.CREATE_FEDERATED_TABLE(
   table_name => 'DEPT_SALES_XT',
   remote_schema_name => 'SHARED_SCHEMA',
   remote_schema_object_name => 'SALES_DATA',
   db_ocids => '[{"region": "IAD", "db_ocid": "OCID1.AUTONOMOUSDATABASE.OC1.IAD.EXAMPLE123"}]'
   );
   END;
   /

   Note:

   The Database OCID (db_ocid) must be in Uppercase.
7. The Analyst queries the external table for department analytics:

   SELECT * FROM DEPT_SALES_XT WHERE region = 'West';

Note:

The above code examples can be modified and implemented according to your requirements.

For more information on the above functions and parameters, see DBMS_DATA_ACCESS_SCOPE Package, DBMS_DATA_ACCESS_ADMIN Package and DBMS_DATA_ACCESS Package.

The following table lists the operations involved in the workflow of the creation of Federated Tables by setting scope along with their descriptions:

Operation	Description	User who runs this operation
Define Creation Scope	Defines which Databases are allowed to remotely create Table Hyperlinks in a Provider Autonomous AI Database instance. See Creation Scope for more information.	Provider
Grant Register	Grants which users in a Provider Autonomous AI Database instance are allowed to register or update scope. See Grant Register for more information.	Provider
Register Creation Scope	Defines which database objects may have table hyperlinks created under a specified authorization scope. See Register Creation Scope for more information.	Provider
Update Creation Scope	Modifies existing creation scope. See Update Creation Scope for more information.	Provider
Unregister Creation Scope	Removes previously registered creation-scope settings for a schema, a single object, or a list of objects. See Unregister Creation Scope for more information.	Provider
Retrieve Creation Scope	Queries and retrieves registered creation scopes for specified schemas or objects. See List Creation Scopes for more information.	Provider
Create Federated Table	Creates a federated table. See Create Federated Table for more information.	Consumer
Grant Read	Grants read privileges to a consumer user for remote schemas or objects, enabling federated table creation. See Grant Read for more information.	Consumer
Revoke Read	Revokes a previously granted read privilege to create a federated table. See Revoke Read for more information.	Consumer
Drop Federated Table	Removes specified federated tables from the database. See Drop Federated Table for more information.	Consumer

• Creation Scope
  A creation scope in a provider database determines which consumer Autonomous AI Database instances are permitted to remotely create Table Hyperlinks in the provider database.
• Grant Register
  The ADMIN (or PDB_DBA) in the provider Autonomous AI Database uses the DBMS_DATA_ACCESS_ADMIN.GRANT_REGISTER to decide which local users are allowed to manage creation scopes.
• Register Creation Scope
  A provider calls DBMS_DATA_ACCESS_SCOPE.REGISTER_CREATION_SCOPE to register the allowed scope for specific tables or entire schemas to create Table Hyperlinks into those objects.​
• Update Creation Scope
  If a provider needs to change access, the same or another authorized user invokes UPDATE_CREATION_SCOPE to broaden, narrow, or otherwise adjust which consumer Autonomous AI Databases can create hyperlinks for particular schema objects.
• Unregister Creation Scope
  When a provider wants to revoke the remote creation capability entirely (for a table, list of tables, or a schema), the provider calls UNREGISTER_CREATION_SCOPE. This removes the registered scope and prevents any new Table Hyperlinks from being created for those objects by consumer databases.​
• Retrieve Creation Scopes
  At any point, an authorized user can call LIST_CREATION_SCOPES to retrieve current scope definitions.
• Grant Read
  In the consumer Autonomous AI Database, the ADMIN (or PDB_DBA) uses DBMS_DATA_ACCESS_ADMIN.GRANT_READ to grant specific users the right to create federated tables against remote provider schemas or objects.
• Revoke Read
  The REVOKE_READ procedure removes a user’s privilege to create federated tables over a specified remote schema or schema object in the provider database.
• Create Federated Table
  A consumer user who has been granted read privileges invokes DBMS_DATA_ACCESS.CREATE_FEDERATED_TABLE to create a federated table over the provider’s in-scope table or view.
• Drop Federated Table
  When the federated access is no longer required, the consumer user calls DBMS_DATA_ACCESS.DROP_FEDERATED_TABLE to remove the federated table.

Creation Scope

A creation scope in a provider database determines which consumer Autonomous AI Database instances are permitted to remotely create Table Hyperlinks in the provider database.

The scope can be defined at multiple levels:

• Tenancy-based (MY$TENANCY) - Any database in the same tenancy as the provider database.
• Compartment-based (MY$COMPARTMENT) - Any database in the same compartment as the provider database.
• Object-level - Specific tables or views within a schema.
• Schema-level - All objects within a specific schema.

You can manage creation scopes using the DBMS_DATA_ACCESS_SCOPE package, which provides procedures to register, unregister, update, and retrieve scopes. See DBMS_DATA_ACCESS_SCOPE for more information.

Grant Register

The ADMIN (or PDB_DBA) in the provider Autonomous AI Database uses the DBMS_DATA_ACCESS_ADMIN.GRANT_REGISTER to decide which local users are allowed to manage creation scopes.

Only these privileged users can register, update, or unregister scopes, preventing uncontrolled exposure of provider tables.

Example

BEGIN
DBMS_DATA_ACCESS_ADMIN.GRANT_REGISTER(
username => 'ANALYTICS_ADMIN',
scope => 'MY$COMPARTMENT'
);
END;
/

The above example grants the ANALYTICS_ADMIN user permission to register data sets within the MY$COMPARTMENT scope.

See GRANT_REGISTER for syntax reference.

Register Creation Scope

A provider calls DBMS_DATA_ACCESS_SCOPE.REGISTER_CREATION_SCOPE to register the allowed scope for specific tables or entire schemas to create Table Hyperlinks into those objects.​

Example: Register Schema-Level Scope

BEGIN
DBMS_DATA_ACCESS_SCOPE.REGISTER_CREATION_SCOPE(
schema_name => 'ANALYTICS',
schema_object_name => 'NULL',
scope => 'MY$COMPARTMENT'
);
END;
/

This example registers a data access scope at the schema level for the ANALYTICS schema, associating it with the compartment MY$COMPARTMENT.

Example: Register Object-Level Scope

BEGIN
DBMS_DATA_ACCESS_SCOPE.REGISTER_CREATION_SCOPE(
schema_name => 'ANALYTICS',
schema_object_name => 'SALES_DATA',
scope => 'MY$TENANCY'
);
END;
/

This example registers a more targeted data access scope for a specific object SALES_DATA within the ANALYTICS schema, linking it to the tenancy MY$TENANCY. Unlike the schema-level version, schema_object_name limits enforcement to just this table, enabling granular control over creation permissions.

Example: Register Multiple Objects

BEGIN
DBMS_DATA_ACCESS_SCOPE.REGISTER_CREATION_SCOPE(
schema_name => 'ANALYTICS',
schema_object_list => '["SALES_DATA", "CUSTOMER_DATA", "PRODUCT_DATA"]',
scope => 'MY$COMPARTMENT'
);
END;
/

This procedure associates the listed schema objects—SALES_DATA, CUSTOMER_DATA, and PRODUCT_DATA with the scope MY$COMPARTMENT, restricting their creation or access to entities within that compartment.

See Register Creation Scope for syntax reference.

Update Creation Scope

If a provider needs to change access, the same or another authorized user invokes UPDATE_CREATION_SCOPE to broaden, narrow, or otherwise adjust which consumer Autonomous AI Databases can create hyperlinks for particular schema objects.

Example

BEGIN
DBMS_DATA_ACCESS_SCOPE.UPDATE_CREATION_SCOPE(
schema_name => 'ANALYTICS',
schema_object_name => 'SALES_DATA',
scope => 'MY$COMPARTMENT'
);
END;
/

This example updates the creation scope for the SALES_DATA table in the ANALYTICS schema to MY$COMPARTMENT.​

See Update Creation Scope for syntax reference.

Unregister Creation Scope

When a provider wants to revoke the remote creation capability entirely (for a table, list of tables, or a schema), the provider calls UNREGISTER_CREATION_SCOPE. This removes the registered scope and prevents any new Table Hyperlinks from being created for those objects by consumer databases.​

Example

BEGIN
DBMS_DATA_ACCESS_SCOPE.UNREGISTER_CREATION_SCOPE(
schema_name => 'ANALYTICS',
schema_object_name => 'SALES_DATA'
);
END;
/

This example block removes a previously registered creation scope for SALES_DATA in the ANALYTICS schema, so that new objects created under that scope are no longer governed by the data access controls the scope enforced.

See Unregister Creation Scope for syntax reference.

Retrieve Creation Scopes

At any point, an authorized user can call LIST_CREATION_SCOPES to retrieve current scope definitions.

Example

DECLARE
l_result CLOB;
BEGIN
DBMS_DATA_ACCESS_SCOPE.LIST_CREATION_SCOPES(
schema_name => 'ANALYTICS',
result => l_result
);
DBMS_OUTPUT.PUT_LINE(l_result);
END;
/

See Retrieve Creation Scope for syntax reference.

Grant Read

In the consumer Autonomous AI Database, the ADMIN (or PDB_DBA) uses DBMS_DATA_ACCESS_ADMIN.GRANT_READ to grant specific users the right to create federated tables against remote provider schemas or objects.

Example: Grant Access to Specific Remote Object

BEGIN
DBMS_DATA_ACCESS_ADMIN.GRANT_READ(
username => 'BI_ANALYST',
remote_schema_name => 'ANALYTICS',
remote_schema_object_name => 'SALES_DATA'
);
END;
/

This example grants the BI_ANALYST user read access to the specific remote SALES_DATA object in the ANALYTICS schema of an external data provider.

See Grant Read for syntax reference.

Revoke Read

The REVOKE_READ procedure removes a user’s privilege to create federated tables over a specified remote schema or schema object in the provider database.

If you omit the remote object name, it revokes that user’s federated-table access for all objects in the given remote schema.​

Example

BEGIN
DBMS_DATA_ACCESS_ADMIN.REVOKE_READ(
username => 'BI_ANALYST',
remote_schema_name => 'ANALYTICS',
remote_schema_object_name => 'SALES_DATA'
);
END;
/

See Revoke Read for syntax reference.

Create Federated Table

A consumer user who has been granted read privileges invokes DBMS_DATA_ACCESS.CREATE_FEDERATED_TABLE to create a federated table over the provider’s in-scope table or view.

The procedure uses database OCIDs and region information to establish Table Hyperlinks so the consumer can query remote data as if it were a local external table.​

Prerequisites

• User must be granted read privileges via GRANT_READ procedure.
• User must exist in both consumer and provider databases with required privileges.
• Consumer database must belong to provider's creation scope.
• Provider must have registered creation scope for target objects.
• Network connectivity established between consumer and provider databases.

Example: Single Provider

BEGIN
DBMS_DATA_ACCESS.CREATE_FEDERATED_TABLE(
table_name => 'SALES_FED',
remote_schema_name => 'ANALYTICS',
remote_schema_object_name => 'SALES_DATA',
db_ocids => '[{"region": "IAD", "db_ocid": "OCID1.AUTONOMOUSDATABASE.OC1.IAD.EXAMPLE123"}]'
);
END;
/

In the above example, a consumer calls DBMS_DATA_ACCESS.CREATE_FEDERATED_TABLE to build a local federated table that links to a remote provider's table or view (e.g., SALES_DATA in ANALYTICS schema), using JSON db_ocids to specify provider database OCIDs and regions for seamless querying as if local.

Once a consumer creates a federated table. They can be queried like regular external tables:

SELECT
REGION,
PRODUCT_ID,
SUM(SALES_AMOUNT) as total_sales,
COUNT(*) as transaction_count
FROM SALES_FED
GROUP BY REGION, PRODUCT_ID
ORDER BY total_sales DESC;

See Create Federated Table for syntax reference.

Drop Federated Table

When the federated access is no longer required, the consumer user calls DBMS_DATA_ACCESS.DROP_FEDERATED_TABLE to remove the federated table.

This procedure cleans up the consumer-side object and effectively ends that particular federated access path, while the underlying provider scopes and privileges remain under provider control.

BEGIN
DBMS_DATA_ACCESS.DROP_FEDERATED_TABLE(
table_name => 'SALES_FED'
);
END;
/

The above example drops the SALES_FED table.

See Drop Federated Table for syntax reference.

Troubleshooting Scenarios

This section provides instructions on the types of failure that can occur and how to troubleshoot the issues.

1. Consumer Database Not in Scope

   Issue: Consumer receives authorization error when attempting to create federated tables.

   Resolution

   • Verify consumer database ID matches provider's scope criteria.
   • Confirm user has been granted read privileges via GRANT_READ procedure.
   • Check provider has registered appropriate creation scope using the REGISTER_CREATION_SCOPE procedure.
   • Verify network connectivity between consumer and provider brokers.
   • Confirm database registration status in OCI Console.

2. Federated Table Creation Fails

   Issue: The CREATE_FEDERATED_TABLE procedure fails despite prerequisites being met.

   Resolution

   • Verify user exists in both consumer and provider databases.
   • Confirm user has object ownership or GRANT OPTION privilege on remote objects.
   • Check remote schema and object names are correct (case-sensitive).
   • Verify db_ocids JSON contains valid region codes and database OCIDs.
   • Ensure provider scope is enabled and contains consumer database.
   • Review broker communication logs for errors.

3. Authorization Errors

   Issue: Users cannot register scopes or access federated tables.

   Resolution

   • Verify user has been granted REGISTER privilege using the GRANT_REGISTER procedure (provider-side).
   • Verify user has been granted READ privilege using the GRANT_READ (consumer-side).
   • For object-level operations, confirm user owns object or has GRANT option privilege.
   • For schema-level operations, confirm user is ADMIN or has PDB_DBA role.
   • Check privilege revocation history to ensure privileges haven't been explicitly revoked.