联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Viewing Information About CDBs and PDBs
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在ORACLE 12C中引入了CDB和PDB的概念,实现了ORACLE数据库的可插拔,在一个CDB数据库中,有多个PDB,而每一个PDB又可以理解为一个独立的传统ORACLE 数据库,那为了能够通过一个sql查询获得整个CDB数据库的信息,ORALCE 引入了CDB_*开头的视图,该视图就是在传统的DBA_*视图基础之上增加了CON_ID,用来区分不同的PDB,从而实现了一个简单sql查询在有足够权限的情况下,可以查询所有PDB中信息
ORACLE 12C版本
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0
确定是否是CDB
SQL> SELECT CDB FROM V$DATABASE; CDB --- YES
YES表示该数据库是CDB,如果是NO表示是NO-CDB(普通数据库)
CDB中各容器信息
SQL> COLUMN NAME FORMAT A8
SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
NAME CON_ID DBID CON_UID GUID
-------- ---------- ---------- ---------- --------------------------------
CDB$ROOT 1 1922813718 1 DB85A3D39F8E7703E0431CAAE80A8C44
PDB$SEED 2 4048821679 4048821679 D49B80694E4A449BE0430100007F906F
PDB1 3 3313918585 3313918585 D49BF37938FB4C10E0430100007F6CBD
PDB2 4 3872456618 3872456618 D49BFA33332F4C3EE0430100007FA059
SQL> select CON_NAME_TO_ID('PDB1') FROM DUAL;
CON_NAME_TO_ID('PDB1')
----------------------
3
SQL> SELECT CON_DBID_TO_ID(3313918585) FROM DUAL;
CON_DBID_TO_ID(3313918585)
--------------------------
3
SQL> SELECT CON_UID_TO_ID(3313918585) FROM DUAL;
CON_UID_TO_ID(3313918585)
-------------------------
3
PDB部分信息
SQL> COLUMN PDB_NAME FORMAT A15
SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
PDB_ID PDB_NAME STATUS
---------- --------------- -------------
2 PDB$SEED NORMAL
3 PDB1 NORMAL
4 PDB2 NORMAL
SQL> COLUMN NAME FORMAT A15
SQL> COLUMN RESTRICTED FORMAT A10
SQL> COLUMN OPEN_TIME FORMAT A30
SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
NAME OPEN_MODE RESTRICTED OPEN_TIME
--------------- ---------- ---------- ------------------------------
PDB$SEED READ ONLY NO 12-MAY-13 08.51.53.177 AM
PDB1 READ WRITE NO 12-MAY-13 09.35.48.646 AM
PDB2 MOUNTED 12-MAY-13 08.56.59.859 AM
CDB中查询对象信息
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN OWNER FORMAT A15
SQL> COLUMN TABLE_NAME FORMAT A30
SQL> SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME
2 FROM DBA_PDBS p, CDB_TABLES t
3 WHERE p.PDB_ID > 2
4 AND T.TABLE_NAME='COL$'
5 AND p.PDB_ID = t.CON_ID
6 ORDER BY p.PDB_ID;
PDB_ID PDB_NAME OWNER TABLE_NAME
---------- --------------- --------------- ------------------------------
3 PDB1 SYS COL$
4 PDB2 SYS COL$
查询在CDB中的PDB数据/临时文件信息
SQL> COLUMN PDB_ID FORMAT 999
SQL> COLUMN PDB_NAME FORMAT A8
SQL> COLUMN FILE_ID FORMAT 9999
SQL> COLUMN TABLESPACE_NAME FORMAT A10
SQL> COLUMN FILE_NAME FORMAT A45
SQL> SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
2 FROM DBA_PDBS p, CDB_DATA_FILES d
3 WHERE p.PDB_ID = d.CON_ID
4 ORDER BY p.PDB_ID;
PDB_ID PDB_NAME FILE_ID TABLESPACE FILE_NAME
------ -------- ------- ---------- ---------------------------------------------
2 PDB$SEED 5 SYSTEM +DATA/pdb$seed_system01.dbf
2 PDB$SEED 7 SYSAUX +DATA/pdb$seed_sysaux01.dbf
3 PDB1 9 SYSAUX +DATA/pdb1_pdb$seed_sysaux01.dbf
3 PDB1 10 USERS +DATA/cdb/pdb1_users01.dbf
3 PDB1 8 SYSTEM +DATA/pdb1_pdb$seed_system01.dbf
4 PDB2 13 USERS +DATA/cdb/pdb2_users01.dbf
4 PDB2 12 SYSAUX +DATA/pdb2_pdb$seed_sysaux01.dbf
4 PDB2 11 SYSTEM +DATA/pdb2_pdb$seed_system01.dbf
8 rows selected.
SQL> COLUMN CON_ID FORMAT 999
SQL> COLUMN FILE_ID FORMAT 9999
SQL> COLUMN TABLESPACE_NAME FORMAT A15
SQL> COLUMN FILE_NAME FORMAT A45
SQL> SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
2 FROM CDB_TEMP_FILES
3 ORDER BY CON_ID;
CON_ID FILE_ID TABLESPACE_NAME FILE_NAME
------ ------- --------------- ---------------------------------------------
1 1 TEMP +DATA/cdb/temp01.dbf
2 2 TEMP +DATA/pdbseed_temp01.dbf
3 3 TEMP +DATA/pdb1_temp01.dbf
4 4 TEMP +DATA/pdb2_temp01.dbf
查询PDB的service信息
SQL> COLUMN NETWORK_NAME FORMAT A30 SQL> COLUMN PDB FORMAT A15 SQL> COLUMN CON_ID FORMAT 999 SQL> SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES 2 WHERE PDB IS NOT NULL AND 3 CON_ID > 2 4 ORDER BY PDB; PDB NETWORK_NAME CON_ID --------------- ------------------------------ ------ PDB1 pdb1 3 PDB2 pdb2 4
PDB中可以修改参数
SELECT NAME FROM V$SYSTEM_PARAMETER WHERE ISPDB_MODIFIABLE = 'TRUE' ORDER BY NAME;
查看PDB历史信息
SQL> COLUMN DB_NAME FORMAT A10 SQL> COLUMN CON_ID FORMAT 999 SQL> COLUMN PDB_NAME FORMAT A15 SQL> COLUMN OPERATION FORMAT A16 SQL> COLUMN OP_TIMESTAMP FORMAT A10 SQL> COLUMN CLONED_FROM_PDB_NAME FORMAT A15 SQL> SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME 2 FROM CDB_PDB_HISTORY 3 WHERE CON_ID > 2 4 ORDER BY CON_ID; DB_NAME CON_ID PDB_NAME OPERATION OP_TIMESTA CLONED_FROM_PDB ---------- ------ --------------- ---------------- ---------- --------------- SEEDDATA 3 PDB$SEED UNPLUG 29-APR-13 CDB 3 PDB1 CREATE 01-FEB-13 PDB$SEED CDB 3 PDB$SEED PLUG 01-FEB-13 PDB$SEED SEEDDATA 4 PDB$SEED UNPLUG 29-APR-13 CDB 4 PDB2 CREATE 01-FEB-13 PDB$SEED CDB 4 PDB$SEED PLUG 01-FEB-13 PDB$SEED 6 rows selected.
One thought on “Viewing Information About CDBs and PDBs”