Oracle 12c如何查看CDB&PDBs信息

這篇文章給大家分享的是有關Oracle 12c如何查看CDB&PDBs信息的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

查看CDB&PDBs信息（SQL*PLUS）

V$，GV$，CDB_，CONTAINER_DATA的屬性決定了對哪些PDB可見；

每個容器數(shù)據對象都有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查詢時，這些視圖返回實例范圍的數(shù)據，返回的每一行CON_ID列中都有。但是，您可以查詢與其他容器數(shù)據對象行為相同的等效視圖。以下視圖可以為CDB中的每個容器返回特定的數(shù)據:[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

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.   查詢用戶創(chuàng)建的表和視圖

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;

9.   查詢PDB中可以修改的參數(shù)

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信息”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！