如何使用 cdb 使用者從 pdbs 中進行選擇？

我有幾個儲存過程，它們使用 log miner 分析 oracle 日誌。這些程序在為此方式創建的 CDB 使用者 C##ADMIN 上執行。

現在我想將記錄的值與實時值進行比較，因此我需要能夠從 CDB 訪問每個 pdb。

經過幾次搜尋，我發現了database link一個可能的答案。但是當我試圖創建一個數據庫連結時，它會說ORA-02011: duplicate database link name。當我試圖放棄它時，我得到了ORA-65230: internal database link cannot be altered or dropped.

我嘗試執行以下查詢，但失敗了：（即使連接為/ as sysdba：）

SELECT * FROM ALL_TABLES@testpdb; 

ORA-12541: TNS:no listener
*Cause: The connection request could not be completed because the listener 
     is not running.
*Action: Ensure that the supplied destination address matches one of 
     the addresses used by the listener - compare the TNSNAMES.ORA entry with 
     the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to 
     go by way of an Interchange). Start the listener on the remote machine.

SELECT * FROM test.TABLE1@testpdb;   

ORA-12541: TNS:no listener
*Cause: The connection request could not be completed because the listener 
     is not running.
*Action: Ensure that the supplied destination address matches one of 
     the addresses used by the listener - compare the TNSNAMES.ORA entry with 
     the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to 
     go by way of an Interchange). Start the listener on the remote machine.

所以我的問題是：

• 使用數據庫連結是正確的方法嗎？
• 如果是，如何配置？
• 我該如何使用它？
• 我有其他選擇來實現我的目標嗎？

該錯誤意味著您的偵聽器未執行。開始你的聽眾。

這只是正常工作而不做任何事情：

SQL> create user c##myadmin identified by password;

User created.

SQL> grant dba to c##myadmin container=all;

Grant succeeded.

SQL> alter user c##myadmin set container_data=all container=current;

User altered.

SQL> conn c##myadmin/password
Connected.
SQL> select con_id, name from v$containers;

   CON_ID NAME
---------- ----------
        1 CDB$ROOT
        2 PDB$SEED
        3 PDB1
        4 PDB2

SQL> select count(*) from dba_users@pdb1;

 COUNT(*)
----------
       37

SQL> select count(*) from dba_users@pdb2;

 COUNT(*)
----------
       37

SQL> select db_link from dba_db_links;

DB_LINK
--------------------------------------------------------------------------------
SYS_HUB

另一種選擇是CONTAINERS子句，但它有一些嚴重的限制（查詢的對象必須存在於所有容器中）。

引用自：https://dba.stackexchange.com/questions/283869