Viewing Information About CDBs & PDBs

（一）原理

The data dictionary that stores the metadata for the CDB as a whole is stored only in the system tablespaces. The data dictionary that stores the metadata for a specific PDB is stored in the self-contained tablespaces dedicated to this PDB. The PDB tablespaces contain both the data and metadata for an application back end.

动态视图和数据字典与application container中的共享对象一样，分为metadata link, data link,但没有extend data link。可以在cdb$root中查看：

select OBJECT_NAME,SHARING from dba_objects where sharing='DATA LINK';

select OBJECT_NAME,SHARING from dba_objects where sharing='METADATA LINK';

When the current container is a PDB, an application root, or an application PDB, a user can view data dictionary information for the current container only.

When the current container is the CDB root, a common user can view data dictionary information for the CDB root and for PDBs, application roots, and application PDBs by querying container data objects.

如果动态视图或字典只存于cdb$root，则pdb在被unplugged后不会包含此部分信息; 反之如果是metadata link, 在pdb被插拔后仍保存原来自己的数据

common user在cdb root时可见cdb及所有pdb数据(需要通过container_data设置用户)。而当前容器为pdb则只有此pdb数据可见(无论是否使用comm user)

cdb$root中v$, gv$, cdb_以及DBA_HIST*存放所有容器数据

Container data objects include V$, GV$, CDB_, and some Automatic Workload Repository DBA_HIST* views.  

In a CDB, for every DBA_ view, there is a corresponding CDB_ view. All CDB_ views are container data objects, but most DBA_ views are not. 

v$, gv$, cdb_视图均有con_id列，用于标识此行数据属于哪个容器，查看con_id对应的con_name使用dba_pdbs：

con_id值为0表示数据属于整个CDB

con_id值为1表示数据属于CDB$ROOT

con_id值为2表示属于PDB$SEED

con_id值为3-4098表示属于PDBs

但以下[G]v$视图的con_id永远为0，如果你要查询其它容器数据要用相应的[G]v$con_xxx

The following views behave differently from other [G]V$ views:

[G]V$SYSSTAT

[G]V$SYS_TIME_MODEL

[G]V$SYSTEM_EVENT

[G]V$SYSTEM_WAIT_CLASS

When queried from the CDB root, these views return instance-wide data, with 0 in the CON_ID column for each row returned. However, you can query equivalent views that behave the same as other container data objects. The following views can return specific data for each container in a CDB: 

[G]V$CON_SYSSTAT

[G]V$CON_SYS_TIME_MODEL

[G]V$CON_SYSTEM_EVENT

[G]V$CON_SYSTEM_WAIT_CLASS.

Note:

访问容器数据时容器必须是打开的(restricted mode打开的容器不会在CDB_视图中显示信息)，且查询用户要有权限

1. When querying a container data object, the data returned depends on whether containers are open and on the privileges granted to the user running the query.
2. In an Oracle Real Application Clusters (Oracle RAC) environment, the data returned by container data objects might vary based on the instance to which a session is connected.
3. In a non-CDB, all CON_ID columns in container data objects are 0 (zero).
4. When a container is opened in restricted mode, it is ignored in queries on CDB_ views.

When queried from the root container, CDB_ and V$ views implicitly convert data to the AL32UTF8 character set. If a character set needs more bytes to represent a character when converted to AL32UTF8, and if the view column width cannot accommodate data from a specific PDB, then data truncation is possible.

（二）常用视图

1. 一般查询

1)[G]V$CONTAINERS：查看所有容器信息

COLUMN NAME FORMAT A8

SELECT NAME, CON_ID, OPEN_MODE, RESTRICTED,TOTAL_SIZE,MAX_SIZE FROM V$CONTAINERS ORDER BY CON_ID;

2) dba_container_data:  查看common user的container_data属性

select username, OBJECT_NAME,CONTAINER_NAME from dba_container_data where username='C##TEST';

3) [G]V$DATABASE:

    SELECT NAME,CDB FROM V$DATABASE;   --查看数据库是cdb还是non-cdb

4) [G]V$SYSTEM_PARAMETER：实例级参数值

SELECT NAME FROM V$SYSTEM_PARAMETER  WHERE ISPDB_MODIFIABLE = 'TRUE'  ORDER BY NAME;  --查看pdb可更改参数

5) [G]V$PARAMETER：会话级参数值

2. 查看PDB

1) v$pdbs: 查看pdb打开状态

COLUMN NAME FORMAT A15

COLUMN RESTRICTED FORMAT A10

select name , open_mode, restricted from v$pdbs;

2) cdb_pdbs: 查看pdbs状态

COLUMN PDB_NAME FORMAT A15

select CON_ID, PDB_NAME, STATUS from cdb_pdbs;

NEW: 表示pdb从未打开过，pdb以read write打开后oracle会执行pdb到cdb集成工作,然后把pdb标为normal. 如果第一次以read only打开会报错

NORMAL: 表示pdb可以用了

UNPLUGGED - The PDB has been unplugged. 此只时能drop此pdb

RELOCATING: The PDB is in the process of being relocated to a different CDB.

RELOCATED: The PDB has been relocated to a different CDB.

REFRESHING: The PDB is a refresh PDB.

UNDEFINED: The PDB is in an undefined state.

UNUSABLE - The PDB is being created or an unrecoverable error was encountered during its creation. The PDB cannot be opened while its state is set to UNUSABLE. If the PDB remains in this state because of an error encountered during its creation, it can only be dropped. The alert log can be checked to determine if there was an error during PDB creation.

3) cdb_pdb_history: 查看pdb的历史操作

select pdb_name,operation, OP_TIMESTAMP from  cdb_pdb_history where pdb_name='PDBNEW';

4) PDB_PLUG_IN_VIOLATIONS: 查看pdb只能以restricted打开原因(在使用DBMS_PDB.CHECK_PLUG_COMPATIBILITY过程时也通过此视图查看)

3. CDB_相关:

1)CDB_PROPERTIES：对应DATABASE_PROPERTIES

2){USER|ALL|DBA|CDB}_OBJECTS

3){ALL|DBA|CDB}_SERVICES

4){USER|ALL|DBA|CDB}_VIEWS

5){USER|ALL|DBA|CDB}_TABLES

6){USER|ALL|DBA|CDB}_USERS

7){USER|ALL|DBA|CDB}_ROLE

8){USER|ALL|DBA|CDB}_COL_PRIVS

9){USER|DBA|CDB}_SYS_PRIVS

10){USER|DBA|CDB}_ROLE_PRIVS

11)ROLE_TAB_PRIVS

12)ROLE_SYS_PRIVS

13){USER|ALL|DBA|CDB}_CLUSTER

14){USER|ALL|DBA|CDB}_CONSTRAINTS

15){ALL|DBA|CDB}_DIRECTORIES

16){USER|ALL|DBA|CDB}_PROCEDURES

17){USER|ALL|DBA|CDB}_SOURCE

18){USER|ALL|DBA|CDB}_SYNONYMS

4. Application相关：

1) {CDB|DBA}_APPLICATIONS

select APP_NAME,APP_VERSION,APP_STATUS from  cdb_applications;

2) {CDB|DBA}_APP_VERSIONS

3) {CDB|DBA}_APP_PATCHES

4) cdb|dba_objects: 查看共享对象属于哪个application

select owner, object_name, object_type, sharing from dba_objects where object_name='T1';

select a.owner, a.object_name, a.OBJECT_TYPE, b.app_name, a.CREATED_VSNID, a.MODIFIED_VSNID from dba_objects a, dba_applications b where a.CREATED_APPID=b.app_id and b.app_name='APP1';

5){CDB|DBA}_APP_ERRORS：Describes all of the application error messages generated in an application container.

6){CDB|DBA}_APP_STATEMENTS：记录在begin/end之间执行语句

1. 其它相关视图

1. {CDB|DBA}_HIST_PDB_INSTANCE：

Displays the PDBs and instances in the Workload Repository.

1. {CDB|DBA}_PDB_SAVED_STATES：

Displays information about the current saved PDB states in the CDB.

1. {CDB|DBA}_CDB_RSRC_PLANS：

Displays information about all the CDB resource plans.

1. {CDB|DBA}_CDB_RSRC_PLAN_DIRECTIVES：

Displays information about all the CDB resource plan directives.

1. [G]V$PDB_INCARNATION：

Displays information about all PDB incarnations. Oracle creates a new PDB incarnation whenever a PDB is opened with the RESETLOGS option.

1. PDB_ALERTS： 

Contains descriptions of reasons for PDB alerts.