Query Remote Database Metadata Views

Use remote database metadata views to retrieve information about remote tables and views that you access with database links.

When you access remote tables from an Autonomous AI Database instance using a database link, you can query these views:

• ALL_REMOTE_TABLES to retrieve information about tables in remote databases.

• ALL_REMOTE_VIEWS to retrieve information about views in remote databases.

The READ privilege on these views is granted to the ADMIN user WITH GRANT OPTION and to the PDB_DBA role without GRANT OPTION.

The following examples show how to query remote database metadata with sample data. Queries on real-time remote databases might return more data.

ALL_REMOTE_TABLES View

Query ALL_REMOTE_TABLES to retrieve information about tables in remote databases.

SELECT owner, table_name, db$name FROM all_remote_tables;

OWNER                TABLE_NAME                          DB$NAME
-------------------- ----------------------------------- --------------------
accuweather          forecast_daily_calendar_metric      DATABRICKS3
bakehouse            media_customer_reviews              DATABRICKS3
bakehouse            sales_transactions                  DATABRICKS3
DG4ODBC              person                              MYSQLDB1
DG4ODBC              products                            MYSQLDB1
DG4ODBC              sales_data                          MYSQLDB1
...

ALL_REMOTE_VIEWS View

Query ALL_REMOTE_VIEWS to retrieve information about views in remote databases.

SELECT owner, view_name, db$name FROM all_remote_views;

OWNER                VIEW_NAME                           DB$NAME
-------------------- ----------------------------------- --------------------
DG4ODBC              ALL_PART_TABLES                     MYSQLDB1
DG4ODBC              ALL_TAB_PARTITIONS                  MYSQLDB1
DG4ODBC              partition_metadata_view             MYSQLDB1