pdb使用越来越多，如何更高效率的管理维护pdb呢，Oracle提供了CDB_开头的视图以供工程师使用，那么如何使用呢？

下面以CDB_USERS视图为例，我们看看是怎么使用的

--查看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上添加相关表

示例输出

--创建用户和表
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 结合使用。具体可参考本博其他文章

【PDB】Oracle跨PDB视图查询