In the past, we have discussed accessing table hyperlinks (also
known as PAR URLs) using external tables. This post focuses on a new enhancement in the Autonomous Database – Shared Infrastructure
(ADB-S) platform called Federated Tables.
Federated Tables automate the creation of table hyperlinks, map
them to external tables, and provide read-only SQL access to remote
data—without requiring manual PAR URL management.
In this example, the setup is done between two databases running
in OCI:
- Source
(Producer): Oracle Autonomous Database 19c
- Target
(Consumer): Oracle Autonomous Database 26ai
Both databases reside within the same OCI compartment.
Step 1: Grant Scope Registration
Privilege on the Source Database
First, grant the scope registration privilege to
the schema that owns the data in the source database.
admin@ATP19C> exec
dbms_data_access_admin.grant_register('DEMO',scope=>'MY$COMPARTMENT');
PL/SQL procedure successfully
completed.
This grants the DEMO schema permission to share data at the compartment level.
Step 2: Register the Creation Scope for Shared Objects
Next, the data owner registers the creation scope for specific
objects. This authorizes consumer databases in the same compartment to create
table hyperlinks remotely.
demo@ATP19C> begin
2 dbms_data_access_scope.register_creation_scope(
3 schema_name => user
4 , schema_object_list => '["DEPT","BIG_TABLE"]'
5 , scope=>'MY$COMPARTMENT' );
6 end;
7* /
PL/SQL procedure successfully completed.
demo@ATP19C>
This step explicitly defines which objects can be
shared. To make this sharing to happen at schema level (or) for all the (existing/upcoming)
objects in the schema we can pass schema_object_list parameter
as NULL
Step 3: Grant Read Access on
the Target (Consumer) Database
On the target database, grant the required privileges to the
consumer schema.
admin@ADB26ai> grant
execute on dbms_data_access to DEMO_USER;
Grant succeeded.
Then below grant will provide read access to the
remote schema and object to the target schema DEMO_USER in the consumer
database.
Step 4: Create the Federated
Table on the Consumer Database
admin@ADB26ai> begin
2 DBMS_DATA_ACCESS_ADMIN.GRANT_READ(
3 username => 'DEMO_USER'
4 , remote_schema_name => 'DEMO'
5 , remote_schema_object_name => 'DEPT' );
6 end;
7* /
PL/SQL procedure successfully completed.
Finally, create the federated external table using the CREATE_FEDERATED_TABLE procedure.
demo-user@ADB26ai> begin
2 dbms_data_access.create_federated_table(
3 table_name => 'MY_DEPT_DEMO'
4 , remote_schema_name =>'DEMO'
5 , remote_schema_object_name =>'DEPT'
6 , db_ocids => '[{"region":"IAD","db_ocid":"OCID1.AUTONOMOUSDATABASE.OC1.IAD.ANUWCLJS3CXJV6AALSKNDVN2XUPBL46LP53FXJQYGOOU5K77X7BTIX2GIFKQ"}]' );
7 end;
8* /
PL/SQL procedure successfully completed.
The region and db_ocid in the above method, refers to the source (producer) database where the data resides.
Step 5: Query the Federated
Table
Once created, the federated table can be queried like a regular
table.
demo-user@ADB26ai> select * from MY_DEPT_DEMO ;
DEPTNO DNAME LOC
_________ _____________ ___________
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Inspecting the table metadata shows that the
federated table is implemented as an external table backed by a table hyperlink.
demo-user@ADB26ai> select dbms_metadata.get_ddl('TABLE','MY_DEPT_DEMO') ;
DBMS_METADATA.GET_DDL('TABLE','MY_DEPT_DEMO')
________________________________________________________________
CREATE TABLE "DEMO_USER"."MY_DEPT_DEMO"
( "DEPTNO" NUMBER,
"DNAME" VARCHAR2(14) COLLATE "USING_NLS_COMP",
"LOC" VARCHAR2(13) COLLATE "USING_NLS_COMP"
) DEFAULT COLLATION "USING_NLS_COMP"
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "DATA_PUMP_DIR"
ACCESS PARAMETERS
( RECORDS ESCAPE CHARACTERSET AL32UTF8 ALLOW MISSING FILES DELIMITED BY NEWLINE NOLOGFILE NOBADFILE NODISCARDFILE READSIZE=10000000
FIELDS CSV WITHOUT EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL NOTRIM )
LOCATION
( 'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/8ldGfvy2qeSeJzDaZ_rtf2aTjutCJAURrI5yKLrk8z743dvC64fYj4IwkUzp54cw9dsHPKpaPjc/data*')
)
REJECT LIMIT 0
PARALLEL
The LOCATION clause points to a secure HTTPS
endpoint, which is the generated table hyperlink.
The same table hyperlink can
be accessed using any REST client (such as curl, Postman, or Insomnia)
$ curl --location 'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/8ldGfvy2qeSeJzDaZ_rtf2aTjutCJAURrI5yKLrk8z743dvC64fYj4IwkUzp54cw9dsHPKpaPjc/data'
{
"items": [
{
"DEPTNO": 10,
"DNAME": "ACCOUNTING",
"LOC": "NEW YORK"
},
{
"DEPTNO": 20,
"DNAME": "RESEARCH",
"LOC": "DALLAS"
},
{
"DEPTNO": 30,
"DNAME": "SALES",
"LOC": "CHICAGO"
},
{
"DEPTNO": 40,
"DNAME": "OPERATIONS",
"LOC": "BOSTON"
}
],
"hasMore": false,
"limit": 1000,
"offset": 0,
"count": 4,
"links": [
{
"rel": "self",
"href": "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/8ldGfvy2qeSeJzDaZ_rtf2aTjutCJAURrI5yKLrk8z743dvC64fYj4IwkUzp54cw9dsHPKpaPjc/data"
}
]
}
Federated Tables in ADB-S
provide a secure and streamlined way to:
- Share data across Autonomous
Databases
- Eliminate manual PAR URL
handling
- Access remote data using
standard SQL
- Consume the same data via
REST endpoints
This feature significantly
simplifies cross-database data sharing while maintaining strong access
controls.
