Manage Catalogs with DBMS

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.

The set of objects available through a database link
A set of shared objects available through DBMS_SHARE (e.g. Delta Sharing);
A set of Iceberg tables managed by an Iceberg REST Catalog;
A set of objects defined by a third party product, such as Amazon Glue, Databricks Unity Catalog, or Snowflakes Polaris;
A set of objects defined by the Oracle OCI Data Catalog Service.
A Catalog can also be thought of as a "Domain", or "Data Product", which is a set of objects grouped together for a specific business purpose.

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.

The Iceberg REST Catalog endpoint;
A credential (e.g. a bearer token) used to call this endpoint;
A credential (e.g. a username/password) used to access the bucket where the Iceberg data files are stored.

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:

CATALOG_TYPE_SHARE:
Represents a share-based catalog, which is designed primarily for sharing data between systems or users. For example, a data sharing service like Data Studio's Data Sharing uses share-based catalogs to distribute data securely.
CATALOG_TYPE_DATA_CATALOG: Represents external data catalogs such as AWS Glue or OCI Data Catalog. These catalogs serve as centralized repositories for metadata about datasets. For instance, the AWS Glue Data Catalog automatically discovers and manages metadata of data sources across AWS services like S3 and Redshift, allowing seamless data integration and querying.
CATALOG_TYPE_DB_LINK: Represents external data catalogs such as AWS Glue or OCI Data Catalog. These catalogs serve as centralized repositories for metadata about datasets. For instance, the AWS Glue Data Catalog automatically discovers and manages metadata of data sources across AWS services like S3 and Redshift, allowing seamless data integration and querying.
See Load Data from Oracle and Non-Oracle Databases using Database Links for more information.
CATALOG_TYPE_VIRTUAL: Represents virtual catalogs that provide an abstraction over physical data sources. Virtual catalogs do not store data themselves but present a unified interface to query disparate data sources — for example, virtual views created in a data virtualization platform.
CATALOG_TYPE_ICEBERG: Represents an Iceberg catalog, which manages metadata for tables stored in the Apache Iceberg format. Iceberg is a table format designed for huge analytic datasets, supporting features like schema evolution and time travel.
See Manage Catalogs for more information.

Constants

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

TYPE_CATALOG and NS_CATALOG: This defines the catalog type and namespace as CATALOG.
DEFAULT_CATALOG: The default catalog name set to LOCAL.

Property constants such as PROP_IS_ENABLED, PROP_IS_SYNCHRONIZED, PROP_CACHE_ENABLED, PROP_CACHE_DURATION, and others for managing catalog behavior and metadata caching.

Constant Name	Value	Description
`PROP_IS_ENABLED`	`IS_ENABLED`	This property determines whether a catalog is currently enabled for query and search. Valid Values: YES: The catalog will appear in the Data Studio UI search dialog and can be used in SQL queries using the database link syntax (e.g. select username from all_users@catalog). NO: The catalog will be listed in `ALL_MOUNTED_CATALOGS` view, but will not be included in Data Studio UI searches and cannot be used in database link syntax.
`PROP_CACHE_ENABLED`	`CACHE_ENABLED`	This property determines whether metadata from a remote schema should be cached in the local database to improve performance. Valid Values YES The metadata will be cached on first access and will remain in the cache for a period specified by `PROP_CACHE_DURATION`. NO: The metadata will not be cached. All attempts to access objects within the catalog will reach out, at query time, to the remote metadata source.
`PROP_CACHE_DURATION`	`CACHE_DURATION`	The number of seconds that metadata should be held in the cache before it becomes stale. The default value is 3600 seconds (one hour). Note that the user may flush the cache manually at any point by calling `DBMS_CATALOG.FLUSH_CATALOG_CACHE`. The user may refresh the cache at any point by calling `DBMS_CATALOG.PREFILL_CATALOG_CACHE`.
`PROP_CACHE_ASYNC`	`'CACHE_ASYNC'`	This field displays if the cache be populated using asynchronous jobs. Valid Values YES: The metadata will be cached using a DBMS_SCHEDULER job that is created the first time a user requests the metadata. NO: The metadata will cached user the database session of the user who requests the metadata.
`PROP_DEFAULT_SCHEMA`	`DEFAULT_SCHEMA`	The name of a remote schema that will be chosen by default if the user runs a query of the form: `select * from table@catalog` The default schema plays the same role as the user's own schema in the local database.
`PROP_DCAT_TYPE`	`DATA_CATALOG_TYPE`	The type of a catalog mounted by `DBMS_CATALOG.MOUNT_DATA_CATALOG` Valid Values AWS_GLUE: The catalog is defined on top of a remote AWS Glue repository. OCI_DCAT: The catalog is defined on top of an OCI Data Catalog instance. Note: This is a read only property.
`PROP_CUSTOM`	`CUSTOM`	A custom property is a name/value pair associated, by the user, with a catalog, schema, table, or other object within the catalog. There are two ways to specify a custom property: You can set a single custom property, "`MY_PROP`" say, by specifying the composite property name '`CUSTOM:MY_PROP`' along with an arbitrary string value. `DBMS_CATALOG.UPDATE_CATALOG_PROPERTY( catalog_name => 'some_catalog', catalog_property => 'CUSTOM:MY_PROP', new_value => 'Property Value');` You can specify a group of customer properties by specify the simple property name 'CUSTOM' along with a JSON object that contains name value pairs. `DBMS_CATALOG.UPDATE_CATALOG_PROPERTY( catalog_name => 'some_catalog', catalog_property => 'CUSTOM', new_value => '{"Property1" : "Value 1", "Property2" : "Value 2", ...}');` Note: Custom property names are case sensitive, so `CUSTOM:MY_PROP` is distinct from `CUSTOM:My_Prop`. To remove a custom property, set the value to NULL. Custom properties can be used as search terms in the Data Studio UI. For example, you can find all tables with the property MY_PROP by specifying the conditions parameter in `DBMS_CATALOG.GET_TABLES`. `SELECT table_name FROM DBMS_CATALOG.GET_TABLES( catalog_name => 'some_catalog', conditions => '#MY_PROP');` You can also search for specific values. `SELECT table_name FROM DBMS_CATALOG.GET_TABLES( catalog_name => 'some_catalog', conditions => '#MY_PROP="Property Value"');`
`PROP_METADATA`	`METADATA`	Custom metadata is similar to custom properties, but it can contain free-form JSON. The metadata is stored with the object and can be retrieved, but it is not used as a search term. There are two ways to specify custom metadata: You can set the entire metadata, as JSON, by using the property name 'METADATA'. `DBMS_CATALOG.UPDATE_CATALOG_PROPERTY( catalog_name => 'some_catalog', catalog_property => 'METADATA', new_value => '{"subObject":{"propName":"abc"}}');` You can update a sub-component of the metadata by using the composite property name 'METADATA:path', where path is some relative JSON path. `DBMS_CATALOG.UPDATE_CATALOG_PROPERTY( catalog_name => 'some_catalog', catalog_property => 'METADATA:subObject.propName', new_value => 'xyz');`
`PROP_CONFIGURATION`	`CONFIGURATION`	The configuration property is used to update specific configuration properties for Iceberg catalogs. It works in a similar way to the CUSTOM property. `BEGIN dbms_catalog.update_catalog_property( 'iceberg_cat', 'CONFIGURATION:IS_CASE_SENSITIVE', 'YES'); END; /`

Data Types

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

credential_info and credential_info_map: These are structures to store and map credential information.
catalog_table and catalog_tables: These are records and tables for storing detailed table metadata (e.g., owner, name, description, status).
catalog_schema and catalog_schemas: These are records and tables for schema metadata.
catalog_object and catalog_objects: These are records and tables for object metadata within a catalog.