【PDB】Oracle跨PDB视图查询

来源：这里教程网时间：2026-03-03 16:50:38 作者：

pdb使用越来越多，如何更高效率的管理维护pdb呢，Oracle提供了CDB_开头的视图以供工程师使用，那么如何使用呢？
下面以CDB_USERS视图为例，我们看看是怎么使用的
``` sql
--查看CDB_USERS类型
SQL> col object_name for a20
SQL> select object_name,object_type from dba_objects where object_name='CDB_USERS';
OBJECT_NAME	     OBJECT_TYPE
-------------------- -----------------------
CDB_USERS	     VIEW
CDB_USERS	     SYNONYM
SQL> 
--查看创建视图的语句
SQL> select dbms_metadata.get_ddl('VIEW','CDB_USERS') from dual;
DBMS_METADATA.GET_DDL('VIEW','CDB_USERS')
--------------------------------------------------------------------------------
  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."CDB_USERS"  CONTAINER_DATA
 ("USERNAME", "USER_ID", "PASSWORD", "ACCOUNT_STATUS", "LOCK_DATE", "EXPIRY_DATE
", "DEFAULT_TABLESPACE", "TEMPORARY_TABLESPACE", "LOCAL_TEMP_TABLESPACE", "CREAT
ED", "PROFILE", "INITIAL_RSRC_CONSUMER_GROUP", "EXTERNAL_NAME", "PASSWORD_VERSIO
NS", "EDITIONS_ENABLED", "AUTHENTICATION_TYPE", "PROXY_ONLY_CONNECT", "COMMON",
"LAST_LOGIN", "ORACLE_MAINTAINED", "INHERITED", "DEFAULT_COLLATION", "IMPLICIT",
 "ALL_SHARD", "PASSWORD_CHANGE_DATE", "CON_ID", "CON$NAME", "CDB$NAME", "CON$ERR
NUM", "CON$ERRMSG") AS
  SELECT k."USERNAME",k."USER_ID",k."PASSWORD",k."ACCOUNT_STATUS",k."LOCK_DATE",
k."EXPIRY_DATE",k."DEFAULT_TABLESPACE",k."TEMPORARY_TABLESPACE",k."LOCAL_TEMP_TA
BLESPACE",k."CREATED",k."PROFILE",k."INITIAL_RSRC_CONSUMER_GROUP",k."EXTERNAL_NA
ME",k."PASSWORD_VERSIONS",k."EDITIONS_ENABLED",k."AUTHENTICATION_TYPE",k."PROXY_
ONLY_CONNECT",k."COMMON",k."LAST_LOGIN",k."ORACLE_MAINTAINED",k."INHERITED",k."D
EFAULT_COLLATION",k."IMPLICIT",k."ALL_SHARD",k."PASSWORD_CHANGE_DATE",k."CON_ID"
, k.CON$NAME, k.CDB$NAME, k.CON$ERRNUM, k.CON$ERRMSG FROM CONTAINERS("SYS"."DBA_
USERS") k
```
如上所示，cdb_视图是结合每个pdb的dba_users，通过CONTAINERS子句 查看所有pdb内容，以下是官方说明(Oracle 21C）
> The CONTAINERS() clause in SQL issued from the CDB root or application root.
Using this clause, you can query data across all PDBs plugged in to the container
root.
使用注意事项：
- 要查询的PDB均有与cdb相同的 表，及相关列。如果列不同，oracle会以cdb表列为主，pdb列被忽略，也可通过alter为表添加列。
- 可根据需求，只在有相关需求的pdb上添加相关表
示例输出
``` sql
--创建用户和表
create user c##mytest identified by oracle;
grant create session,create view to c##mytest container=current;
grant set container,create table,unlimited tablespace to c##mytest;
create table c##mytest.test2021 (id int);
--pdb
create table c##mytest.test2021 (id int);
--查询
select * from containers(c##mytest.test2021);
--查看 用户
select username from containers(dba_users);
```
当然，containers子句不好判断是哪个pdb下的，可以通过cdb_users 和视图v$containers 结合使用。 具体可参考本博其他文章