48 DBMS_CATALOG

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

48.1 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