Federated Tables in ADB-S : Simplifying Cross-Database Data Access

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.