Oracle Cloud Infrastructure Documentation

Database Link Notes

While accessing remote tables from an Autonomous AI Database instance using a DBLink, you can query the following.

  • 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 are granted to the ADMIN user WITH GRANT OPTION and to the PDB_DBA role without GRANT OPTION.

Here are a few examples of querying remote databases with sample data. A query on real-time remote databases might return a lot more data.

Example: Query using ALL_REMOTE_TABLES.
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
…
Example: Query using ALL_REMOTE_VIEWS.
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