Manage Catalogs with DBMS_CATALOG

The DBMS_CATALOG package provides a comprehensive set of procedures, functions, and types for managing database catalogs for Oracle Autonomous AI Database.

What is a Catalog?

A catalog is defined to be a collection of named schemas, each of which contains a collection of named objects, such as TABLES or VIEWS. Every Oracle database has a single local catalog - the data dictionary. This catalog is always present and cannot be removed. But there are other examples of catalogs, defined outside the database.

For more information on the Catalog tool of the Data Studio suite of tools, refer to The Catalog Tool.

In an operating system you can access external data files by mounting a file system. By analogy, you can access external data in an Autonomous AI Database by mounting a catalog using the DBMS_CATALOG package.

The following example shows how to mount an external Iceberg REST Catalog. To use it, you would need three pieces of information.

BEGIN
  -- Create a credential capable of accessing an external Iceberg REST catalog
  dbms_cloud.create_credential('ICEBERG_CATALOG_CRED', ...);

  -- Create a credential capable of accessing the bucket where the
  -- Iceberg data files are stored.
  dbms_cloud.create_credential('ICEBERG_STORAGE_CRED', ...);

  -- Mount the iceberg catalog
  dbms_catalog.mount_iceberg(
    catalog_name             => 'ICEBERG_CAT',
    endpoint                 => 'https://...',
    catalog_credential       => 'ICEBERG_CATALOG_CRED',
    data_storage_credential  => 'ICEBERG_STORAGE_CRED',
    catalog_type             => 'ICEBERG_UNITY');
END;
/

-- List tables in the iceberg catalog
SELECT owner, table_name
FROM all_tables@iceberg_cat;

-- Read data from an iceberg table
SELECT *
FROM a_schema.a_table@iceberg_cat;

The DBMS_CATALOG is used to define and handle operations related to the catalog.

It provides a comprehensive set of procedures, functions, and types for managing catalogs within Oracle Autonomous database. It supports operations such as mounting and unmounting catalogs, managing catalog properties, handling credentials, and working with catalog entities like tables, schemas, and objects. This package is essential for integrating external data sources and managing metadata in a secure and efficient manner.

Security Model

The DBMS_CATALOG package operates under the AUTHID CURRENT_USER model, meaning it runs with the privileges of the current user. You must have appropriate permissions to perform operations such as mounting catalogs, updating properties, or managing credentials.

Note: Few catalogs require Autonomous AI Database to connect to external internet resources. Therefore, you must add the relevant external addresses to the access control lists for the associated database user. For example, when enabling access to a Databricks Unity Iceberg catalog on Azure, you may need to whitelist two addresses: one for accessing the Iceberg REST APIs and another if the user requires access to the underlying data.

BEGIN
   dbms_network_acl_admin.append_host_ace(
      host => '*.azuredatabricks.net',
      lower_port => 443,
      upper_port => 443,
      ace => xs$ace_type(
      privilege_list => xs$name_list('http', 'http_proxy'),
      principal_name => 'DBUSER',
      principal_type => xs_acl.ptype_db));

   dbms_network_acl_admin.append_host_ace(
      host => '*.blob.core.windows.net',
      ace => xs$ace_type(
      privilege_list => xs$name_list('http', 'http_proxy'),
      principal_name => 'DBUSER',
      principal_type => xs_acl.ptype_db));
END;
/

You must have the DWROLE to run the DBMS_CATALOG methods.

Run the following command to grant DWROLE to the user:

GRANT DWROLE TO MY_USER;

Catalog Types and Constants

This section provides an overview of the catalog types and key constants defined in the package. It explains the different catalog categories used to organize various catalog sources and outlines essential constants for configuring and managing catalog properties and behaviors.

Catalog Types

The package specifies multiple catalog types to classify and handle different categories of catalogs. They are:

Constants

The following section highlights the essential constants that define catalog properties and settings. The constants include:

Data Types

The DBMS_CATALOG package introduces various custom data types to manage catalog-related information. They are: