ITPub博客

首页 > 数据库 > Oracle > Oracle 12c 查看CDB&PDBs信息(SQL*PLUS)

Oracle 12c 查看CDB&PDBs信息(SQL*PLUS)

Oracle 作者:panpong 时间:2019-04-11 17:17:49 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16976507/viewspace-2641078/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
数据库工程师,熟悉oracle,postgresql,mysql,DB2等主流关系型数据库运维,调优,一直活跃在运维一线。了解Python,django,简单自动化运维系统开发。

注册时间:2009-03-06

  • 博文量
    137
  • 访问量
    326047