Oracle Cloud Infrastructure Documentation

About Querying Remote Catalogs and Databases

This topic describes how to query external data using the catalog metadata using an SQL syntax of the form: [schema].object@catalog_name.

Here the syntax [schema].object@catalog_name refers to a representation of accessing database objects

The table describes the parameters and their descriptions used in the syntax:

Parameters Description
Schema

This field is optional, and it defaults to the current user. A schema is a namespace within a catalog that organizes related database objects such as tables, views, procedures, or functions. It acts as a logical container for these objects.
Object This field refers to any database object contained within a schema, such as tables, views, procedures, or functions.
Catalog The catalog field displays the name of the catalog with the referenced table. You can create and manage catalogs using the DBMS_CATALOG package or the Catalog Tool of the Data Studio suite of tools.
Note

You can replace catalogs with dbLinks as well.

Accessing Remote Tables

To access a table from a remote schema using a catalog, you must follow these steps:
  1. Mount a Catalog: Begin by mounting a catalog over any supported type of data store (e.g., Iceberg, Delta Lake, etc.). Mounting a catalog standardizes the metadata, enabling SQL access to external data as if it were tables within a schema in the catalog. Refer to Manage Catalogs for detailed steps on mounting catalogs. Once the catalog is mounted and metadata is standardized, you can directly query external data using SQL. Even if the external data does not physically exist as traditional tables, the catalog abstracts it into a tabular format for easy querying.
  2. Reference the table: Once mounted, you can reference the remote table by specifying the schema and catalog (if applicable) followed by the table name using the following syntax: [remote_schema].table@catalog.

Here the schema in this identifier is the remote schema that contains the remote table. Table, in this case, is the table name in the remote catalog.

Note

The user must have the appropriate credentials to access the specific table and catalog.

Key Points to Consider

  • The feature is designed using the DBMS_CATALOG package installed in an ADB-S environment.
  • DDL operations (e.g., CREATE, ALTER, DROP) are not allowed on remote databases via catalogs. If you do so, you may encounter an "ORA-02021: DDL operations are not allowed on a remote database" error.
  • Same name Restriction: This feature enforces that for a given user, there cannot exist a DB link and a catalog with the same name.

In the subsequent chapters, you will learn how to:

You will use SQL Worksheet Editor of Database Actions to run SQL queries. You must first connect to your Database Actions instance and select SQL under Development menu.

Prerequisites:

You will require an OCI account.

How are DBlinks different from Catalogs?

DBLinks: They are primarily used to connect and query another database directly using a syntax.

For example:
SELECT * FROM <schema_name>.<table_name>@<dblink_name>;

When a catalog is mounted over another database via a DBLink, there is no need to separately define metadata in the catalog because the DBLink syntax directly accesses the remote database objects. Essentially, the DBLink acts as a transparent connection to the remote database, allowing queries to run as if the data were local.

Catalogs: They serve as a metadata layer that can be mounted over different types of data stores, not only databases connected via DBLinks but also external data catalogs or data shares like Delta Share or Live Share providers. When a catalog is mounted over a non-database store, the @catalog syntax adapts queries to generate inline external tables, enabling access to external data without requiring a DBLink. This means catalogs abstract the metadata management and provide a unified interface for querying diverse data sources, including external ones.