联系:手机/微信(+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.
《Viewing Information About CDBs and PDBs》有 1 条评论