查看 CDB&PDBs 信息（ SQL*PLUS ）
V$
，
GV$
，
CDB_
，
CONTAINER_DATA
的属性决定了对哪些
PDB
可见；
每个容器数据对象都有
CON_ID
列，列值
代表整个
CDB
可见，
1
代表
root
对象，
2
代表
seed
对象
,3~254
代表
PDB
对象；
以下视图的行为不同于其他
[G]V$
视图
:
·
[G]V$SYSSTAT
·
[G]V$SYS_TIME_MODEL
·
[G]V$SYSTEM_EVENT
·
[G]V$SYSTEM_WAIT_CLASS
从
root
查询时，这些视图返回实例范围的数据，返回的每一行
CON_ID
列中都有
。但是，您可以查询与其他容器数据对象行为相同的等效视图。以下视图可以为
CDB
中的每个容器返回特定的数据
:[G]V$CON_SYSSTAT
、
[G]V$CON_SYS_TIME_MODEL
、
[G]V$CON_SYSTEM_EVENT
和
[G]V$CON_SYSTEM_WAIT_CLASS
。
1. CDB 中的视图 views
Table 43-2 Views for a CDB
|
View |
Description |
|
Container data objects, including: l
V$
views
l
GV$
views
l
CDB_
views
l
DBA_HIST*
views
|
Container data objects can display information about multiple PDBs. Each container data object includes a
CON_ID
column to identify containers.
There is a
CDB_
view for each corresponding
DBA_
view.
|
|
{CDB|DBA}_PDBS
|
Displays information about the PDBs associated with the CDB, including the status of each PDB. |
|
CDB_PROPERTIES
|
Displays the permanent properties of each container in a CDB. |
|
{CDB|DBA}_PDB_HISTORY
|
Displays the history of each PDB. |
|
{CDB|DBA}_CONTAINER_DATA
|
Displays information about the user-level and object-level
CONTAINER_DATA
attributes specified in the CDB.
|
|
{CDB|DBA}_HIST_PDB_INSTANCE
|
Displays the PDBs and instances in the Workload Repository. |
|
{CDB|DBA}_PDB_SAVED_STATES
|
Displays information about the current saved PDB states in the CDB. |
|
{CDB|DBA}_CDB_RSRC_PLANS
|
Displays information about all the CDB resource plans. |
|
{CDB|DBA}_CDB_RSRC_PLAN_DIRECTIVES
|
Displays information about all the CDB resource plan directives. |
|
PDB_ALERTS
|
Contains descriptions of reasons for PDB alerts. |
|
PDB_PLUG_IN_VIOLATIONS
|
Displays information about incompatibilities between a PDB and the CDB to which it belongs. This view is also used to display information generated by executing
DBMS_PDB.CHECK_PLUG_COMPATIBILITY
.
|
|
{USER|ALL|DBA|CDB}_OBJECTS
|
Displays information about database objects, and the
SHARING
column shows whether a database object is a metadata-linked object, an object-linked object, or a standalone object that is not linked to another object.
|
|
{ALL|DBA|CDB}_SERVICES
|
Displays information about database services, and the
PDB
column shows the name of the PDB associated with each service.
|
|
{USER|ALL|DBA|CDB}_VIEWS
{USER|ALL|DBA|CDB}_TABLES
|
The
CONTAINER_DATA
column shows whether the view or table is a container data object.
|
|
{USER|ALL|DBA|CDB}_USERS
|
The
COMMON
column shows whether a user is a common user or a local user.
|
|
{USER|ALL|DBA|CDB}_ROLES
{USER|ALL|DBA|CDB}_COL_PRIVS
{USER|ALL}_COL_PRIVS_MADE
{USER|ALL}_COL_PRIVS_RECD
{USER|ALL}_TAB_PRIVS_MADE
{USER|ALL}_TAB_PRIVS_RECD
{USER|DBA|CDB}_SYS_PRIVS
{USER|DBA|CDB}_ROLE_PRIVS
ROLE_TAB_PRIVS
ROLE_SYS_PRIVS
|
The
COMMON
column shows whether a role or privilege is commonly granted or locally granted.
|
|
{USER|ALL|DBA|CDB}_ARGUMENTS
{USER|ALL|DBA|CDB}_CLUSTERS
{USER|ALL|DBA|CDB}_CONSTRAINTS
{ALL|DBA|CDB}_DIRECTORIES
{USER|ALL|DBA|CDB}_IDENTIFIERS
{USER|ALL|DBA|CDB}_LIBRARIES
{USER|ALL|DBA|CDB}_PROCEDURES
{USER|ALL|DBA|CDB}_SOURCE
{USER|ALL|DBA|CDB}_SYNONYMS
{USER|ALL|DBA|CDB}_VIEWS
|
The
ORIGIN_CON_ID
column shows the ID of the container from which the row originates.
|
|
[G]V$DATABASE
|
Displays information about the database from the control file. If the database is a CDB, then CDB-related information is included. |
|
[G]V$CONTAINERS
|
Displays information about the containers associated with the current CDB, including the root and all PDBs. |
|
[G]V$PDBS
|
Displays information about the PDBs associated with the current CDB, including the open mode of each PDB. |
|
[G]V$PDB_INCARNATION
|
Displays displays information about all PDB incarnations. Oracle creates a new PDB incarnation whenever a PDB is opened with the
RESETLOGS
option.
|
|
[G]V$SYSTEM_PARAMETER
[G]V$PARAMETER
|
Displays information about initialization parameters, and the
ISPDB_MODIFIABLE
column shows whether a parameter can be modified for a PDB.
|
2. 如何判断是否 CDB
SELECT CDB FROM V$DATABASE;
3. 查询 CDB 中的容器信息
Example 43-2 Viewing Identifying Information About Each Container in a CDB COLUMN NAME FORMAT A8 SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
4. 查询 PDB 信息
Example 43-3 Viewing Container ID, Name, and Status of Each PDB COLUMN PDB_NAME FORMAT A15 SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
5. 查询 PDB 的 open mode
COLUMN NAME FORMAT A15 COLUMN RESTRICTED FORMAT A10 COLUMN OPEN_TIME FORMAT A30 SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS ;
6. 查询 container data objects
Example 43-5 Showing the Tables Owned by Specific Schemas in Multiple PDBs COLUMN PDB_NAME FORMAT A15 COLUMN OWNER FORMAT A15 COLUMN TABLE_NAME FORMAT A30 SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME FROM DBA_PDBS p, CDB_TABLES t WHERE p.PDB_ID > 2 AND t.OWNER IN('HR','OE') AND p.PDB_ID = t.CON_ID ORDER BY p.PDB_ID;
Example 43-6 Showing the Users in Multiple PDBs COLUMN PDB_NAME FORMAT A15 COLUMN USERNAME FORMAT A30 SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME FROM DBA_PDBS p, CDB_USERS u WHERE p.PDB_ID > 2 AND p.PDB_ID = u.CON_ID ORDER BY p.PDB_ID;
Example 43-7 Showing the Data Files for Each PDB in a CDB COLUMN PDB_ID FORMAT 999 COLUMN PDB_NAME FORMAT A8 COLUMN FILE_ID FORMAT 9999 COLUMN TABLESPACE_NAME FORMAT A10 COLUMN FILE_NAME FORMAT A45 SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME FROM DBA_PDBS p, CDB_DATA_FILES d WHERE p.PDB_ID = d.CON_ID ORDER BY p.PDB_ID;
Example 43-8 Showing the Temp Files in a CDB COLUMN CON_ID FORMAT 999 COLUMN FILE_ID FORMAT 9999 COLUMN TABLESPACE_NAME FORMAT A15 COLUMN FILE_NAME FORMAT A45 SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME FROM CDB_TEMP_FILES ORDER BY CON_ID;
Example 43-9 Showing the Services Associated with PDBs COLUMN NETWORK_NAME FORMAT A30 COLUMN PDB FORMAT A15 COLUMN CON_ID FORMAT 999 SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES WHERE PDB IS NOT NULL AND CON_ID > 2 ORDER BY PDB;
7. 查询用户创建的表和视图
Example 43-10 Querying a Table Owned by a Common User Across All PDBs
SELECT * FROM CONTAINERS(employees);
Example 43-11 Querying a Table Owned by Local Users Across All PDBs
CREATE OR REPLACE VIEW employees AS SELECT * FROM hr.employees;
SELECT * FROM CONTAINERS(employees);
SELECT * FROM CONTAINERS(employees) WHERE CON_ID IN(3,4);
8. 查询当前 container 的 ID 和 name
SHOW CON_ID
SHOW CON_NAME
Example 43-12 Returning the Container ID Based on the Container Name
SELECT CON_NAME_TO_ID('HRPDB') FROM DUAL;
Example 43-13 Returning the Container ID Based on the Container DBID
SELECT CON_DBID_TO_ID(2226957846) FROM DUAL;
|
Function |
Description |
|
CON_NAME_TO_ID
('
container_name
')
|
Returns the container ID based on the container's name. |
|
CON_DBID_TO_ID
(
container_dbid
)
|
Returns the container ID based on the container's DBID. |
|
CON_UID_TO_ID
(
container_uid
)
|
Returns the container ID based on the container's unique identifier (UID). |
|
CON_GUID_TO_ID
(
container_guid
)
|
Returns the container ID based on the container's globally unique identifier (GUID). |
9. 查询 PDB 中可以修改的参数
SELECT NAME FROM V$SYSTEM_PARAMETER
WHERE ISPDB_MODIFIABLE = 'TRUE'
ORDER BY NAME;
10. 查询 PDB 历史记录
COLUMN DB_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN PDB_NAME FORMAT A15
COLUMN OPERATION FORMAT A16
COLUMN OP_TIMESTAMP FORMAT A10
COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;
