主页 / dba / 问题 / 283869
Accepted
SHR
Asked: 2021-01-25 07:15:15 +0800 CST

如何使用 cdb 用户从 pdbs 中进行选择？

  • 772

我有几个存储过程，它们使用 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.

所以我的问题是：

  • 使用数据库链接是正确的方法吗？
  • 如果是，如何配置？
  • 我该如何使用它？
  • 我有其他选择来实现我的目标吗？
oracle oracle-19c

1 个回答

  1. Best Answer

    该错误意味着您的侦听器未运行。开始你的听众。

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

    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子句，但它有一些严重的限制（查询的对象必须存在于所有容器中）。

    • 1

相关问题