DBMS_CATALOG

The DBMS_CATALOG package is a core Oracle Data Studio functionality and enabling technology for Iceberg support.

About the DBMS_CATALOG Package

The DBMS_CATALOG package is PL/SQL package that provides a broad set of subprograms for catalog management, organized by functional area.

Oracle Data Studio previously was available only with Oracle Autonomous AI Database. Starting with Oracle Database 26ai ( 23.26.2), you can now install this feature as an option for your database. For installation details see Oracle AI Database Utilities.

The DBMS_CATALOG package features include the following:

• Catalog lifecycle management (mount/enable/disable/detach):

  • Add (mount) a new catalog by mounting an existing connection (for example, mounting an existing database link) or by creating a new connection and registering it as a catalog.

  • Enable or disable a catalog.

  • Detach or remove a catalog from the database.

Note: Disabled catalogs retain connection details and metadata but are not updated and are not accessible from user interfaces (UIs) or programmatic access (APIs).

• Property management: Retrieve and update properties for catalogs, schemas, and tables, including support for large text values (CLOB).

• Credential model (required or local credentials):

  • A mounted catalog can define one or more required credentials that you must provide before the catalog can be used.

    For example, Iceberg catalogs typically have two required credentials:

    • ICEBERG_CREDENTIAL (to call Iceberg REST Catalog endpoints; typically a bearer token credential)

    • DATA_STORAGE_CREDENTIAL (to access the Iceberg data files in a bucket)

  • A local credential is a credential object in the user’s schema that is mapped to one of the required credentials.

  • You can set local credentials during mount (for example, using DBMS_CATALOG.MOUNT_ICEBERG arguments such as catalog_credential and data_storage_credential) or later by using SET_LOCAL_CREDENTIAL.

  • You can view current mappings with GET_LOCAL_CREDENTIAL_MAP (returns JSON).

• Catalog entity management: Create, drop, and list catalog-related objects, including:

  • Cloud storage links associated with catalogs

  • External tables within catalogs

  • Schemas and their contents

  • Lists of schemas, tables, or other catalog objects with optional filtering

• Data access and caching:

  • Generate SELECT statements for logical tables (customizable).

  • Open cursors to fetch data from logical tables.

  • Manage metadata caching (flush or prefill) to keep catalog metadata current.

• Synchronization and sharing:

  • Use CREATE_SYNCHRONIZED_SCHEMAS and DROP_SYNCHRONIZED_SCHEMAS to create or remove local schemas synchronized with remote catalog definitions.

  • Use UPDATE_SYNCHRONIZED_SCHEMA_PROPERTY to modify synchronized schema properties (using CLOB values).

  • Use GRANT_TO_RECIPIENT and REVOKE_FROM_RECIPIENT to manage recipient access to shared catalogs.

For details about using this package refer to Database Actions Using Guide for Oracle Cloud

Related Topics

• “DBMS_CATALOG Package” in Database Actions Using Guide for Oracle Cloud