CREATE ICEBERG TABLE

Purpose

Use CREATE ICEBERG TABLE to create a table on an external catalog that has no representation in the database. You must use functions and procedures offered by Data Studio's DBMS_CATALOG package to access Iceberg tables and catalogs. The catalog needs to be defined with dbms_catalog.mount_iceberg.

To access the table after creation run the following command, if the catalog's Oracle Database representation does not identify the table:

EXEC DBMS_CATALOG.FLUSH_CATALOG_CACHE('<catalog name>');

See Also:

• The Data Studio Overview Pageof Using Oracle Autonomous AI Database Serverless

• Manage Catalogs with DBMS_CATALOG of Using Oracle Autonomous AI Database Serverless

Prerequisites

You must set up the ACL to be able to reach the Iceberg catalog server and object storage endpoints.

You must have the ADPUSER role, which is part of Data Studio installation.

Syntax

Description of the illustration create_iceberg_table.eps

Semantics

Native iceberg table creation will support the same data types as DMLs, including decimal, float, double, string, date, and timestamp.

• catalog_name must be an Iceberg REST catalog that is mounted using the DBMS_CATALOG package

• bucket_uri_or_prefix must be an S3-compatible endpoint

Restrictions

• Must comply with the ICEBERG V2 specification.

• Only the following Iceberg table column data types are supported:

  • DECIMAL

  • FLOAT

  • DOUBLE

  • STRING (maximum length of the string that can be read is 4000 CHAR)

  • DATE

  • TIMESTAMP

• Iceberg tables cannot be partitioned.

• If there is an open transaction, issuing an Iceberg ITAS statement will raise an error.

• After you issue an Iceberg ITAS statement, you must issue a commit or rollback. Before commit or rollback is finished,

  • DMLs on any Oracle table or Iceberg table are not allowed

  • Queries on the Iceberg table that was the insertion target are not allowed

  • Queries on other Oracle or Iceberg tables are allowed.

• Select query result in ITAS must have the same schema as Iceberg table.

• The database must have AL32UTF8 NLS_CHARACTERSET.

• Parquet compression level = ZSTD Level 3

• DBMS_CLOUD package is available.

• DBMS_CATALOG package is available (Data Studio).

• The sum of the iceberg schema name, table name, and catalog name (used for mount_iceberg) must not exceed 113 ASCII characters.

• Constraints are not supported for Iceberg table creation DDL.

• $ and # symbols are not supported in the Iceberg table name.

• Iceberg table column names must be <= 128 ASCII characters for supporting ITAS.

• A reference to the table (schema_name.table_name@catalog_name) should appear only once in the SQL statement.