Query Apache Iceberg tables from Oracle Autonomous AI Database

Apache Iceberg is an open table format for lakehouses: Parquet (or ORC, Avro) data files in object storage, plus metadata files that track the table's schema, partitions and versions. Oracle Autonomous AI Database (formerly Autonomous Database) can read an Iceberg table in place through an external table. You query lakehouse data, and join it to your own tables, with plain SQL and without copying it into the database.

When an Iceberg external table fits

• Your data already lives in a lakehouse that another engine writes (Spark, Databricks, Snowflake, AWS Glue jobs, BigQuery), and you want to query it from Autonomous AI Database.
• You want to join lakehouse data with tables in the database, for reports, APEX pages or analytics, without building a copy pipeline first.
• You only need to read it. An Iceberg external table can be queried but not changed with DML; the engine that owns the table keeps writing it.
• Your heaviest queries filter on the columns the table is partitioned by (a date, a region, a customer or tenant ID). Those are the queries partition pruning helps.

Load the data into a database table instead if you must update it from the database, or if every query reads the whole table anyway.

How it works

Each change to an Iceberg table creates a snapshot: a complete, read-only version of the table. A metadata file (a JSON document) lists the snapshots and names the current one. Each snapshot points to a manifest list, which names the manifest files for that snapshot. The manifests list the data files with their partition values and column statistics. A catalog (AWS Glue, Polaris, Unity and others) records which metadata file is current. When you create the external table, Autonomous AI Database takes the column names and types from the Iceberg metadata, so you don't pass a column list. When you query it, the database reads the metadata first, then only the data files it needs.

1. Choose catalog-managed or no catalog

Supported catalogs and storage

• AWS Glue with Amazon S3. Use the same credentials for S3 and Glue, and keep the bucket and the catalog in the same AWS region.
• Snowflake Polaris with Amazon S3 or Azure ADLS Gen2, authenticated with OAuth2 client credentials or another Polaris-supported token. Oracle says Snowflake Horizon Catalog works with the same steps.
• Databricks Unity Catalog with Amazon S3 or Azure ADLS Gen2, for UniForm tables only: Delta tables that also write Iceberg metadata. Unity's own Iceberg tables (not UniForm) can't be read this way.
• Google Lakehouse runtime catalog (formerly BigLake) with Google Cloud Storage, for BigQuery-created Iceberg tables only, read-only.
• Hadoop catalog on OCI Object Storage (for example, tables written by OCI Data Flow).
• Direct metadata.json on Amazon S3, Azure ADLS Gen2 or OCI Object Storage.

2. Prepare the schema and network access

• An Autonomous AI Database and a schema to own the external tables.
• DWROLE granted to that schema. It's the standard role for working with DBMS_CLOUD.
• Network ACLs for that schema: outbound HTTPS (port 443) to every host the table needs, such as objectstorage.<region>.oraclecloud.com, <bucket>.s3.<region>.amazonaws.com, glue.<region>.amazonaws.com, <workspace>.azuredatabricks.net, <account>.snowflakecomputing.com, <storage-account>.blob.core.windows.net, or biglake.googleapis.com, oauth2.googleapis.com and <bucket>.storage.googleapis.com. Add your HTTPS proxy host too if you use one. A user who only queries an existing table needs no network ACL.
• A storage credential to read the metadata and data files. Every configuration needs one.
• A catalog credential too, for REST catalogs (Polaris, Unity, Google). The external table then uses two: credential_name for the files and rest_auth_cred for the catalog.
• The table's location: the catalog type, region or endpoint and table path, or the URI of the metadata.json file.

-- From Oracle's documented syntax, not run. Run as ADMIN.
GRANT DWROLE TO <schema>;

-- Outbound HTTPS for the schema that CREATES the external table.
-- One call per host your configuration needs (see the host list above).
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'objectstorage.<region>.oraclecloud.com',
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(
                    privilege_list => xs$name_list('http'),
                    principal_name => '<schema>',
                    principal_type => xs_acl.ptype_db));
END;
/

3. Create the storage credential

Create the credential as the schema that will own the external table. For OCI Object Storage, use an OCI native credential: an API signing key whose private key has no passphrase. For S3 and Glue, use an access key and secret. For ADLS Gen2, use a storage account key or SAS token. For Google Cloud Storage, use an HMAC access key and secret.

-- From Oracle's documented syntax, not run. Run as <schema>.

-- OCI Object Storage: OCI native (API signing key) credential
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_CRED',
    user_ocid       => '<user_ocid>',
    tenancy_ocid    => '<tenancy_ocid>',
    private_key     => '<private_key_without_passphrase>',
    fingerprint     => '<key_fingerprint>');
END;
/

-- Amazon S3 (also used for AWS Glue): access key and secret
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'S3_CRED',
    username        => '<aws_access_key_id>',
    password        => '<aws_secret_access_key>');
END;
/

4. Create the Iceberg external table

All variants call DBMS_CLOUD.CREATE_EXTERNAL_TABLE with "protocol_type": "iceberg" in the format JSON. Leave column_list and field_list out: they must be NULL, because the columns come from the Iceberg metadata. Pick the variant that matches your catalog.

Option A: AWS Glue catalog

This is the shape of Oracle's own example, which creates ORDERS over a Glue table db.orders. With a catalog, you don't pass file_uri_list.

-- From Oracle's documented syntax, not run.
BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name      => 'ORDERS',
    credential_name => 'S3_CRED',
    format          => '{
      "access_protocol": {
        "protocol_type": "iceberg",
        "protocol_config": {
          "iceberg_catalog_type": "aws_glue",
          "iceberg_glue_region": "<region>",
          "iceberg_table_path": ["<glue_database>.<glue_table>"]
        }
      }
    }'
  );
END;
/

Option B: one metadata.json file (no catalog)

Point file_uri_list at the table's metadata.json and leave out protocol_config. The table is pinned to that snapshot.

-- From Oracle's documented syntax, not run.
BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name      => 'ORDERS_SNAPSHOT',
    credential_name => 'OCI_CRED',
    file_uri_list   => 'https://<bucket-or-container>/<path>/metadata.json',
    format          => '{"access_protocol": {"protocol_type": "iceberg"}}'
  );
END;
/

Option C: Hadoop catalog on OCI Object Storage

For tables written with a Hadoop catalog (for example by OCI Data Flow), give the warehouse folder and the database.table path.

-- From Oracle's documented syntax, not run.
BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name      => 'ORDERS_LAKE',
    credential_name => 'OCI_CRED',
    format          => '{
      "access_protocol": {
        "protocol_type": "iceberg",
        "protocol_config": {
          "iceberg_catalog_type": "hadoop",
          "iceberg_warehouse": "https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/<warehouse_folder>",
          "iceberg_table_path": "<database>.<table>"
        }
      }
    }'
  );
END;
/

Option D: a REST catalog (Snowflake Polaris)

REST catalogs need two credentials. This is Oracle's Polaris shape. For Databricks Unity Catalog and the Google Lakehouse runtime catalog, copy the format JSON from Oracle's provider quickstarts, because each catalog's JSON is different.

-- From Oracle's documented syntax, not run.
-- Catalog credential (OAuth2 client credentials)
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'POLARIS_OAUTH',
    username        => '<client_id>',
    password        => '<client_secret>');
END;
/

-- credential_name reads the files; rest_auth_cred talks to the catalog
BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name      => 'SALES_POLARIS',
    credential_name => 'S3_CRED',
    format          => q'[{
      "access_protocol": {
        "protocol_type": "iceberg",
        "protocol_config": {
          "iceberg_catalog_type": "polaris",
          "rest_catalog_prefix": "<polaris_catalog_name>",
          "rest_catalog_endpoint": "https://<account>.snowflakecomputing.com/polaris/api/catalog",
          "rest_authentication": {
            "rest_auth_cred": "POLARIS_OAUTH",
            "rest_auth_endpoint": "https://<account>.snowflakecomputing.com/polaris/api/catalog/v1/oauth/tokens"
          },
          "table_path": ["<namespace>", "<table>"]
        }
      }
    }]'
  );
END;
/

5. Check that it works

Oracle's quick check is a simple count. If it returns a number, the credentials, ACLs and table location are right. Then grant read access to the users who need it.

-- From Oracle's documented syntax, not run.
SELECT COUNT(*) FROM orders;

-- Readers need SELECT on the table and DWROLE, not the network ACLs
GRANT SELECT ON orders TO <reader>;

6. Query it, and let partition pruning skip files

Query the external table like any other table. Since Oracle's October 2026 update, Autonomous AI Database also uses the partition metadata in Iceberg's manifests to skip data files that can't contain a matching row. There's nothing to enable and no new syntax. You don't define Oracle partitions on the external table, and the partition values aren't added to your results. Results stay the same as a full scan; only the files read change.

• Pruning happens when the query has a supported filter on the Iceberg source column: the column the partition transform is built from. Filter on customer_id, not on a bucket number.
• Oracle's example: orders is partitioned by bucket(4, customer_id). WHERE customer_id = 202 maps to bucket 1, so only bucket 1's data files are read.
• A filter that reaches the Iceberg table only through a join isn't guaranteed to prune. Oracle says not to assume it will.

-- From Oracle's documented syntax, not run.
-- orders is partitioned in Iceberg by bucket(4, customer_id)

-- Prunes: supported filter on the partition source column
SELECT * FROM orders WHERE customer_id = 202;

-- May not prune: the value only reaches orders through the join
SELECT o.*
FROM   orders o JOIN customers c ON c.customer_id = o.customer_id
WHERE  c.customer_id = 202;

-- Same rows (inner equi-join), plus a direct filter that can prune
SELECT o.*
FROM   orders o JOIN customers c ON c.customer_id = o.customer_id
WHERE  c.customer_id = 202
AND    o.customer_id = 202;

-- From Oracle's documented syntax, not run.
-- SQLcl or SQL*Plus: show the elapsed time of each statement
SET TIMING ON

SELECT COUNT(*)
FROM   orders o JOIN customers c ON c.customer_id = o.customer_id
WHERE  c.customer_id = 202;

SELECT COUNT(*)
FROM   orders o JOIN customers c ON c.customer_id = o.customer_id
WHERE  c.customer_id = 202
AND    o.customer_id = 202;

Elapsed time depends on your data, your file layout and your database, so test with your own tables. Oracle publishes no speed-up figure for this feature, and neither do we.

Time travel (optional)

Oracle also documents time travel for Iceberg external tables: AS OF TIMESTAMP reads the latest snapshot at or before the time you give. It's supported with Databricks, AWS Glue and the Hadoop catalog on OCI Object Storage. DBMS_CLOUD.ICEBERG_TABLE_HISTORY lists the snapshots you can choose from. The snapshot's metadata and data files must still exist, so snapshots removed by Iceberg maintenance can't be read. A timestamp earlier than the history the database can see returns an error, and the external table's schema must match the snapshot's schema.

-- From Oracle's documented syntax, not run.
-- 1. List the snapshots Iceberg still keeps
SELECT snapshot_id, committed_at
FROM   TABLE(DBMS_CLOUD.ICEBERG_TABLE_HISTORY('ORDERS'))
ORDER  BY committed_at;

-- 2. Read the table as it was at a point in time
SELECT COUNT(*)
FROM   orders
AS OF TIMESTAMP TO_TIMESTAMP_TZ('<yyyy-mm-dd hh24:mi:ss> UTC', 'YYYY-MM-DD HH24:MI:SS TZR');

Limits

Writers handle row changes in one of two ways. Copy-on-write rewrites the affected data files. Merge-on-read keeps the old files and writes small delete files beside them. This matters for the first limit.

• Merge-on-read changes: if the writer records deletes or updates as separate delete files, your queries error out. Tables that use copy-on-write work.
• Schema changes: the external table's schema is fixed when you create it. If the Iceberg schema changes, recreate the external table.
• Direct-metadata tables don't move forward: they stay on the snapshot in their metadata.json. Recreate them on a newer file to see new data.
• No DML: an Iceberg external table can be queried, not updated.
• Certified REST catalogs: the only REST catalogs Oracle has certified for reading are Snowflake Polaris and Databricks Unity Catalog, and Unity only for UniForm tables. Google support covers BigQuery-created Iceberg tables in GCS only.
• Credentials and catalogs: see the warning in step 2 and the supported list in step 1.
• Time travel: only with Databricks, AWS Glue and the Hadoop catalog on OCI, and only for snapshots that are still retained.

Troubleshooting

• ORA-20000: Failed to generate column list or ORA-24247: network access denied by access control list (ACL) when you create the table: the owning schema usually lacks an ACL for one of the hosts. Check every host, including the HTTPS proxy and, for S3, the regional and non-regional bucket endpoints. If the ACLs are right, check the credential.
• Queries fail on a table that worked yesterday: check whether the writer started producing merge-on-read delete files, or whether the Iceberg schema changed. For a schema change, recreate the external table.
• New data doesn't show up: if the table points at a metadata.json file, it's pinned to that snapshot. Recreate it on the newer file, or switch to a catalog-managed table.
• AWS Glue errors: confirm that the bucket and the Glue catalog are in the same region and that one credential works for both.
• Google Lakehouse runtime catalog stops working: a bearer token may have expired. Update the credential with a new token, or use the service-account email and private key.
• No faster than before: check that the filter is on the partition source column of the Iceberg table itself, not only on a joined table. Compare against the same query with the literal on the source column.

Checklist

1. Choose catalog-managed (production) or no catalog: a Hadoop warehouse folder, or one metadata.json (fixed snapshot).
2. Confirm that your catalog and storage pair is in Oracle's supported list.
3. Grant DWROLE and HTTPS ACLs for every host to the schema that creates the table.
4. Create the storage credential, and a catalog credential for REST catalogs. Use placeholders in anything you share.
5. Create the external table with "protocol_type": "iceberg" and no column list.
6. Run SELECT COUNT(*), then grant SELECT to readers.
7. Filter on the partition source column; don't rely on join-derived filters to prune.
8. Agree with the table's writers to avoid merge-on-read deletes and to warn you before schema changes.
9. Time your heaviest queries with and without a filter on the source column.

Short version of the pruning rule: the Note on Iceberg partition pruning. Sources: Query Apache Iceberg Tables, DBMS_CLOUD subprograms and What's New for Autonomous AI Database Serverless.

Check your understanding