ORACLE常用系統檢視（整理） .

常用系統檢視
2.1 Oracle常用動態檢視
2.1.1 v$lock
給出了鎖的資訊，如type欄位， user type locks有3種：TM，TX，UL，system type locks有多種，常見的有：MR，RT，XR，TS等。我們只關心TM，TX鎖。
當TM鎖時，id1欄位表示object_id；當TX鎖時，trunc(id1/power(2,16))代表了回滾段號。
lmode欄位，session持有的鎖的模式，有6種：
0 - none  
1 - null (NULL)  
2 - row-S (SS)  
3 - row-X (SX)  
4 - share (S)  
5 - S/Row-X (SSX)  
6 - exclusive (X)
request欄位，process請求的鎖的模式，取值範圍與lmode相同。
ctime欄位，已持有或等待鎖的時間。
block欄位，是否阻塞其它鎖申請，當block=1時說明這個session阻塞了別的session。
2.1.2 v$sqlarea
共享池中儲存的sql和一些相關的資訊，如累計的執行次數（executions），邏輯讀（buffer_gets），物理讀（disk_reads）等統計資訊。
根據address和hash_value可以定位一條sql。sql_text欄位存的這個sql的前1000個字元。查詢整個的sql還需去v$sqltext或者v$sqltext_with_newlines。
2.1.3 v$session
V$SESSION是基礎資訊檢視，用於找尋使用者SID或SADDR。不過，它也有一些列會動態的變化，可用於檢查使用者。  
常用列：  
SID：SESSION標識，常用於連線其它列  
SERIAL#：如果某個SID又被其它的session使用的話則此數值自增加(當一個SESSION結束，另一個SESSION開始並使用了同一個SID)。  
AUDSID：審查session ID唯一性，確認它通常也用於當尋找並行查詢模式  
USERNAME：當前session在oracle中的使用者名稱。  
STATUS：這列用來判斷session狀態是：  
Achtive：正執行SQL語句(waiting for/using a resource)  
Inactive：等待操作(即等待需要執行的SQL語句)  
Killed：被標註為刪除  
paddr, process addr, 透過這個欄位我們可以檢視當前程式的相關資訊, 系統程式id,作業系統使用者資訊等等.  
 (sql_address,sql_hash_value) (prev_sql_addr,prev_hash_value) 根據這兩組欄位, 我們可以查詢到當前session正在執行的sql語句的詳細 資訊.

所有當前session的資訊，包括一些使用者資訊如username，terminal，program，logon_time等，command欄位表示session執行的什麼命令。
透過v$session.paddr=v$process.addr跟process相關，透過v$session.taddr=v$transaction.addr跟trancation相關。
透過sid可以到相關檢視中查詢與這個session相關的各種資訊，如v$sesstat中有session的統計資訊，v$sess_io中有session的io統計資訊，v$session_wait中有session的等待資訊，v$session_event中有session在一段時間內所等待的各種事件的資訊。
根據lockwait欄位可以查詢當前正在等待的鎖的相關資訊：
select * from v$lock where kaddr in (select lockwait from v$session where sid=&sid);
(sql_address,sql_hash_value)，(prev_sql_addr,prev_hash_value) 根據這兩組欄位, 可以查詢到當前session正在執行的或者最近一次執行的sql語句的詳細資訊：
select * from v$sqltext where address = &sql_address and hash_value = &sql_hash_value;
2.1.4 v$sesstat
根據session id可以得到這個session的統計資訊：
select a.name,b.value
from v$statname a,v$sesstat b  
where a.STATISTIC#=b.STATISTIC#  
and b.sid=&sid  
and b.value <>0
order by b.value;
2.1.5 v$session_wait
--這部分抄襲 xzh的blog
根據session id可以得到這個session的等待情況。
event欄位表示等待事件。
p1、p2、p3告訴我們等待事件的具體含義，如果wait event是db file scattered read，p1=file_id/p2=block_id/p3=blocks，然後透過dba_extents即可確定出熱點物件；如果是latch free的話，p2為閂鎖號，它指向v$latch。
P1RAW,P2RAW,P3RAW列對應P1,P2,P3的十六進位制值；P1TEXT,P2TEXT,P3TEXT列對應P1,P2,P3列的解釋。
--求等待事件及其對應的latch
col event format a32
col name format a32
select sid,event,p1 as file_id, p2 as "block_id/latch", p3 as blocks,l.name
from v$session_wait sw,v$latch l
where event not like '%SQL%' and event not like '%rdbms%'  
and event not like '%mon%' and sw.p2 = l.latch#(+);
--求等待事件及其熱點物件
col owner format a18
col segment_name format a32
col segment_type format a32
select owner,segment_name,segment_type
from dba_extents
where file_id = &file_id and &block_id between block_id
and block_id + &blocks - 1;
--綜合以上兩條sql，同時顯示latch及熱點物件(速度較慢)
select sw.sid,event,l.name,de.segment_name
from v$session_wait sw,v$latch l,dba_extents de
where event not like '%SQL%' and event not like '%rdbms%'  
and event not like '%mon%' and sw.p2 = l.latch#(+) and sw.p1 = de.file_id(+) and p2 between de.block_id and de.block_id + de.blocks - 1;
--如果是非空閒等待事件，透過等待會話的sid可以求出該會話在執行的sql
select sql_text  
from v$sqltext_with_newlines st,v$session se
where st.address=se.sql_address and st.hash_value=se.sql_hash_value
and se.sid =&wait_sid order by piece;
2.1.6 v$process
v$process檢視包含當前系統oracle執行的所有程式資訊。常被用於將oracle或服務程式的作業系統程式ID與資料庫session之間建立聯絡。  
常用列：  
ADDR：程式物件地址  
PID：oracle程式ID  
SPID：作業系統程式ID  

V$PROCESS中的連線列  
Column View Joined Column(s)  
ADDR V$SESSION PADDR  

根據session id可以查到作業系統程式的資訊：
select * from v$process where addr in (select paddr from v$session where sid=&sid );
spid欄位是作業系統程式號，可以用來進行 kill -9 spid 這麼一個操作。
可以找到 unix命令 top 出來的最耗cpu資源的pid，根據pid最耗資源的sql：
SELECT /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid'))
ORDER BY piece ASC
2.1.7 v$transaction
根據session id可以查到當前session正在執行的事務資訊：
select * from v$transaction where addr in (select taddr from v$session where sid=&sid );
看下面這兩個欄位可以看到事務進行到什麼程度了：
USED_UBLK
NUMBER
Number of undo blocks used
USED_UREC
NUMBER
Number of undo records used
重複查詢這兩個值，可以看到變化，可以估計事務的進度，尤其是長時間的回滾操作，當這兩個值為0，回滾也就完成了。
2.1.8 v$sort_usage
temp表空間的使用情況，當temp表空間變得巨大的時候，根據session_addr可以得到session id，根據sqladdr和sqlhash可以得到正在執行的sql：
select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)) as Space,tablespace,segtype,sql_text  
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size'  
and su.session_addr=se.saddr  
and s.hash_value=su.sqlhash  
and s.address=su.sqladdr  
order by se.username,se.sid;
2.1.9 v$sysstat
所有instance的統計資訊
2.2 監控檢視
2.2.1 v$sqltext檢視  
 v$sqltext檢視包括Shared pool中SQL語句的完整文字，一條SQL語句可能分成多個塊被儲存於多個記錄內。  
常用列：  
HASH_VALUE：SQL語句的Hash值  
ADDRESS：sql語句在SGA中的地址  
SQL_TEXT：SQL文字。  
PIECE：SQL語句塊的序號  

V$SQLTEXT中的連線列  
Column View Joined Column(s) HASH_VALUE, ADDRESS V$SQL, V$SESSION HASH_VALUE, ADDRESS  
HASH_VALUE. ADDRESS V$SESSION SQL_HASH_VALUE, SQL_ADDRESS  

按pid檢視正在執行的程式：  
select sid,program from v$session b where paddr in (select addr from v$process where spid=$pid);  

按pid檢視正在執行的sql語句  
select sql_text from v$sqltext where hash_value in (select sql_hash_value from v$session where   
PADDR in (select addr from v"$process where spid=$pid)) order by piece;  
2.2.2 V$SESSION_WAIT檢視  
這是一個尋找效能瓶頸的關鍵檢視。它提供了任何情況下session在資料庫中當前正在等待什麼(如果session當前什麼也沒在做，則顯示它最後的等待事件)。當系統存在效能問題時，本檢視可以做為一個起點指明探尋問題的方向。 
V$SESSION_WAIT中，每一個連線到例項的session都對應一條記錄。  
常用列：  
SID: session標識  
EVENT: session當前等待的事件，或者最後一次等待事件。  
WAIT_TIME: session等待事件的時間(單位，百分之一秒)如果本列為0，說明session當前session還未有任何等待。 
SEQ#: session等待事件將觸發其值自增長  
P1, P2, P3: 等待事件中等待的詳細資料  
P1TEXT, P2TEXT, P3TEXT: 解釋說明p1,p2,p3事件  
附註：  
1.State欄位有四種含義﹕  
Waiting：SESSION正等待這個事件。  
Waited unknown time：由於設定了timed_statistics值為false，導致不能得到時間資訊。表示發生了等待，但時間很短，中國自學程式設計網， 。  
Wait short time：表示發生了等待，但由於時間非常短不超過一個時間單位，所以沒有記錄。  
Waited knnow time：如果session等待然後得到了所需資源，那麼將從waiting進入本狀態。  
Wait_time值也有四種含義： 值>0：最後一次等待時間(單位：10ms)，當前未在等待狀態。  
值=0：session正在等待當前的事件。  
值=-1：最後一次等待時間小於1個統計單位，當前未在等待狀態。  
值=-2：時間統計狀態未置為可用，當前未在等待狀態。  
3.Wait_time和Second_in_wait欄位值與state相關:  
如果state值為Waiting，那麼wait_time值無用。Second_in_wait值是實際的等待時間(單位：秒)。  
如果state值為Wait unknow time，那麼wait_time值和Second_in_wait值都無用。  
如果state值為Wait short time，那麼wait_time值和Second_in_wait值都無用。  
如果state值為Waiting known time，那麼wait_time值就是實際等待時間(單位：秒)，Second_in_wait值無用。  
V$SESSION_WAIT中的連線列  
Column View Joined Colum  
SID V$SESSION SID  
檢視session等待事件：  
select sid,event from v$session_wait where event not like ’rdbms%’ and event not like ’SQL*Net message%’;  
多數的session都是空閒事件如：SQL*Net message from client, pipe get, PMON timer等。
2.3 和使用者角色許可權相關的檢視
2.2.3 概述
和許可權,角色相關的檢視大概有下面這些:  
DBA_SYS_PRIVS: 查詢某個使用者所擁有的系統許可權  
USER_SYS_PRIVS: 當前使用者所擁有的系統許可權  
SESSION_PRIVS: 當前使用者所擁有的全部許可權  
ROLE_SYS_PRIVS: 某個角色所擁有的系統許可權  
注意: 要以SYS使用者登陸查詢這個檢視,否則返回空.  
ROLE_ROLE_PRIVS: 當前角色被賦予的角色  
SESSION_ROLES: 當前使用者被啟用的角色  
USER_ROLE_PRIVS: 當前使用者被授予的角色  

另外更有針對表的訪問許可權的檢視:  
TABLE_PRIVILEGES  
ALL_TAB_PRIVS   
ROLE_TAB_PRIVS:某個角色被賦予的相關表的許可權  
...   
2.2.4 Examples
1.查詢當前使用者所擁有的許可權
Select * from session_privs;
2.查詢某個使用者被賦予的系統許可權.
  能夠有多種方式  
 Select * from user_sys_privs;  
  或: select * from DBA_SYS_PRIVS where grantee='XXX'  
(需要當前使用者擁有DBA角色)  
3. 查詢當前使用者被授予的角色:
1).Select * from SESSION_ROLES order by ROLE   
 說明: 這個查詢會返回當前使用者所被授予的全部角色, 其中包括巢狀授權的角色. 例如將DBA角色授予了一個使用者,DBA角色已被授予的角色(例如 exp_full_database 和 imp_full_database) 也會被查詢出來.
2). Select * from USER_ROLE_PRIVS
4. 查詢某一角色被賦予的系統許可權
Select Privilege from ROLE_SYS_PRIVS where ROLE=&Role  
輸入 role='CONNECT'  
輸出:
PRIVILEGE
--------------------
ALTER SESSION
CREATE CLUSTER
CREATE DATABASE LINK
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE VIEW  
5. 查詢當前角色被授予的角色
Select GRANTED_ROLE from ROLE_ROLE_PRIVS where ROLE=&ROLE  
輸入 role= 'DBA'  
輸出:  
GRANTED_ROLE
----------------------
DELETE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
PLUSTRACE
SELECT_CATALOG_ROLE  
說明: PLUSTRACE這個角色是用於執行SQL AUTO TRACE的, 透過執行$ORACLE_HOME/sqlplus/admin/plustrce.sql能夠生成這個角色