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 結合使用。 具體可參考本博其他文章

【PDB】Oracle跨PDB檢視查詢

相關文章