如何通过PDB查询关联视图获取CDB信息？

你可以通过关联PDB环境下可访问的CDB级元数据视图，结合现有查询中的PDB_DBID或PDB_NAME来获取CDB信息，以下是几种可行的实现方式：

方法1：通过V$DATABASE直接获取CDB核心信息 #

V$DATABASE在PDB环境中返回的是整个CDB的元数据，无需复杂关联即可拿到CDB名称、DBID等关键信息：

SELECT
    MIN(b.name) AS PDB_NAME,
    MIN(b.dbid) AS PDB_DBID,
    MIN(a.event_timestamp) AS TIMESTAMP,
    d.name AS CDB_NAME,
    d.dbid AS CDB_DBID
FROM audsys.aud$unified a
JOIN V$PDBS b ON a.dbid = b.dbid
CROSS JOIN V$DATABASE d
GROUP BY d.name, d.dbid, a.dbid;

方法2：通过V$CONTAINERS关联CDB根容器信息 #

V$CONTAINERS包含所有容器（包括CDB根容器CDB$ROOT）的信息，通过CON_ID=1定位根容器，同时可关联当前PDB的PDB_DBID确保对应关系：

SELECT
    MIN(b.name) AS PDB_NAME,
    MIN(b.dbid) AS PDB_DBID,
    MIN(a.event_timestamp) AS TIMESTAMP,
    c.name AS CDB_ROOT_NAME,
    c.dbid AS CDB_ROOT_DBID
FROM audsys.aud$unified a
JOIN V$PDBS b ON a.dbid = b.dbid
JOIN V$CONTAINERS c ON c.con_id = 1
GROUP BY c.name, c.dbid, a.dbid;

方法3：通过CDB_PROPERTIES获取CDB名称 #

CDB_PROPERTIES存储了CDB的属性配置，其中PROPERTY_NAME='CDB_NAME'的记录对应CDB的名称：

SELECT
    MIN(b.name) AS PDB_NAME,
    MIN(b.dbid) AS PDB_DBID,
    MIN(a.event_timestamp) AS TIMESTAMP,
    p.property_value AS CDB_NAME
FROM audsys.aud$unified a
JOIN V$PDBS b ON a.dbid = b.dbid
JOIN CDB_PROPERTIES p ON p.property_name = 'CDB_NAME'
GROUP BY p.property_value, a.dbid;

注意事项 #

• 上述视图需要你拥有SELECT_CATALOG_ROLE权限才能访问；
• 你之前尝试的CDB_PDBS视图仅存储PDB的元数据，不包含CDB本身的信息，因此无法通过它获取CDB级数据。

内容的提问来源于stack exchange，提问作者svk