Oracle 12c 檢視CDB&PDBs資訊(SQL*PLUS)
檢視 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
l
l
l
|
Container data objects can display information about multiple PDBs. Each container data object includes a
There is a
|
|
Displays information about the PDBs associated with the CDB, including the status of each PDB. |
|
Displays the permanent properties of each container in a CDB. |
|
Displays the history of each PDB. |
|
Displays information about the user-level and object-level
|
|
Displays the PDBs and instances in the Workload Repository. |
|
Displays information about the current saved PDB states in the CDB. |
|
Displays information about all the CDB resource plans. |
|
Displays information about all the CDB resource plan directives. |
|
Contains descriptions of reasons for PDB alerts. |
|
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
|
|
Displays information about database objects, and the
|
|
Displays information about database services, and the
|
|
The
|
|
The
|
|
The
|
|
The
|
|
Displays information about the database from the control file. If the database is a CDB, then CDB-related information is included. |
|
Displays information about the containers associated with the current CDB, including the root and all PDBs. |
|
Displays information about the PDBs associated with the current CDB, including the open mode of each PDB. |
|
Displays displays information about all PDB incarnations. Oracle creates a new PDB incarnation whenever a PDB is opened with the
|
|
Displays information about initialization parameters, and the
|
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 |
|
Returns the container ID based on the container's name. |
|
Returns the container ID based on the container's DBID. |
|
Returns the container ID based on the container's unique identifier (UID). |
|
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 12c CDB&PDBs管理Oracle
- Oracle 12C 新特性之 sqlplus檢視History命令OracleSQL
- SQLPLUS檢視oracle sql執行計劃命令SQLOracle
- Oracle ASM檢視資訊OracleASM
- Oracle檢視TOP SQLOracleSQL
- Oracle 12C R2-新特性-SQLPLUS提供檢視歷史命令的功能OracleSQL
- 檢視oracle鎖相關資訊Oracle
- Oracle檢視歷史TOP SQLOracleSQL
- 【統計資訊】全面檢視錶所有統計資訊SQLSQL
- oracle 統計資訊檢視與收集Oracle
- Oracle檢視版本號等其他資訊Oracle
- 檢視錶的統計資訊SQL指令碼SQL指令碼
- 【TUNE_ORACLE】檢視錶,列和索引的統計資訊SQL參考Oracle索引SQL
- 透過SQL_ID檢視SQL歷史執行資訊SQL
- 通過SQL_ID檢視SQL歷史執行資訊SQL
- Oracle 12c 使用SQL*Plus來建立與移動應用程式SeedsOracleSQL
- Oracle 12c使用SQL*Plus來建立與刪除應用程式容器OracleSQL
- 檢視各項Oracle資料庫資訊Oracle資料庫
- 【SQL*Plus】使用SQL*Plus的-S選項精簡輸出資訊SQL
- SQL檢視SQL
- Oracle 檢視sql開幾個並行OracleSQL並行
- Oracle 檢視SQL的執行計劃OracleSQL
- 檢視Oracle隱藏引數的SQLOracleSQL
- ORACLE之檢視資料庫的SQLOracle資料庫SQL
- ORACLE SQL and SQL*PLUS (strong recommend)OracleSQL
- Oracle錶的歷史統計資訊檢視Oracle
- Oracle 通過undo塊檢視事務資訊Oracle
- oracle檢視和更新統計表的資訊Oracle
- ORACLE EBS 系統檢視檔案版本資訊Oracle
- Oracle 12c新特性之檢測有用的多列統計資訊Oracle
- oracle sql tuning 8--常用的檢視OracleSQL
- 檢視處理Oracle中被鎖物件的SQLOracle物件SQL
- ORACLE資料庫檢視ACQ(ACTIVE CHECKPOINT QUEUE)資訊Oracle資料庫
- oracle 11g 統計資訊 相關檢視Oracle
- Oracle檢視查詢慢之統計資訊收集Oracle
- Oracle中檢視sql命令歷史,檢視rman命令歷史OracleSQL
- Sql Server基礎:使用T_SQL建立,修改,檢視資料庫資訊SQLServer資料庫
- centos檢視版本資訊CentOS