Oracle12c如何查看CDB&PDBs信息

这篇文章给大家分享的是有关Oracle 12c如何查看CDB&PDBs信息的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

创新互联建站服务项目包括桥东网站建设、桥东网站制作、桥东网页制作以及桥东网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，桥东网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到桥东省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！

查看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 SHARINGcolumn 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 PDBcolumn 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;

感谢各位的阅读！关于“Oracle 12c如何查看CDB&PDBs信息”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

网页标题：Oracle12c如何查看CDB&PDBs信息
转载注明：http://cdiso.cn/article/jjohjp.html