會話管理
?
1：查看當前所有用戶的會話(SESSION)：
SELECT * FROM V$SESSION?WHEREUSERNAMEIS?NOT?NULLORDER?BYLOGO{N}_{T}IME,SID;其中Oracle內部進程的USERNAME為空?2：查看當前用戶的所有SESSION：SELECT?FROMV$SESSION
WHERE USERNAME = USER
ORDER?BY LOGON_TIME, SID;
?
3：查看當前窗口/當前用戶的會話信息
SELECT SID, SERIAL#, STATUS FROM V$SESSION?WHEREAUDSID=USERENV{(}^{\prime }SESSIONI{D}^{\prime });?4：查看所有ACTIVE會話(活動會話)SELECT?FROMV$SESSION?
WHERE USERNAME IS?NOT?NULL?AND STATUS=‘ACTIVE’
ORDER?BY LOGON_TIME, SID;
?
5：查看當前會話的ID可以通過如下腳本:
SELECT * FROM VKaTeX parse error: Expected 'EOF', got '#' at position 66: …S.SID, S.SERIAL#? FROM VPROCESS P
INNER?JOIN V$SESSION S ON P.ADDR = S.PADDR
WHERE S.AUDSID=USERENV(‘SESSIONID’);
?
6：查看數據庫允許最大會話數
SQL> SHOW PARAMETER SESSIONS;
?
NAME???????????????????????????????? TYPE??????? VALUE

---

java_max_sessionspace_size?????????? integer???? 0
java_soft_sessionspace_limit???????? integer???? 0
license_max_sessions???????????????? integer???? 0
license_sessions_warning???????????? integer???? 0
logmnr_max_persistent_sessions?????? integer???? 1
sessions???????????????????????????? integer???? 225
shared_server_sessions?????????????? integer????
?
?
SQL> SELECT NAME, TYPE, VALUE?FROM V$PARAMETER?WHERENAMELIKE{?}^{\prime }session?NAME??????????????????????TYPE????????????????VALUE??????????????????????????????????????????????????????????sessions?????????????????????????3???????????????225sessio{n}_{c}ache{d}_{c}ursors???????????3???????????????20sessio{n}_{m}a{x}_{o}pe{n}_{f}iles???????????3???????????????107：查看曾經的最大會話數：?SQL>SQL>SELECTSESSION{S}_{M}AX,SESSION{S}_{W}ARNING,SESSION{S}_{C}URRENT,SESSION{S}_{H}IGHWATER??2?FROMv$license;
?
SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER

---

0??????????????? 0????????????? 512??????????????? 553
SESSIONS_HIGHWATER表示曾經的最大會話數512
?
8：查詢那些應用的連接數此時是多少
SELECT? B.PROGRAM , COUNT(1)
FROM V$PROCESSA,V$SESSION B
WHERE A.ADDR = B.PADDR
????? AND? B.USERNAME IS?NOT?NULL
GROUP?BY B.PROGRAM;
?
會話狀態：
會話有ACTIVE、INACTIVE、KILLED、CACHED、SNIPED五個狀態，一般比較常見的有ACTIVE、INACTIVE、KILLED三個狀態。
?
ACTIVE?? ：處于此狀態的會話，表示正在執行，處于活動狀態。
INACTIVE ：處于此狀態的會話表示不是正在執行的
KILLED?? ：處于此狀態的會話，表示出現了錯誤或進程被殺掉，正在回滾，當然，這個狀態的會話也占用系統資源的。還有一點就是，??? KILLED的狀態一般會持續較長時間，如果你想快速殺掉回話，可以參考我以前的一篇文章ORACLE快速徹底Kill掉的會話
CACHED?? ： Session temporarily cached for use by OracleXA
SNIPED?? ： Session inactive, waiting on the client。 標記為SNIPED的進程被釋放有兩種條件：
???????? 1、相關的terminal再一次試圖登錄及執行sql
???????? 2、手動的在操作系統后臺kill掉相應的spid
關于會話信息
通過如下SQL你可以查詢你的每個應用程序到底在等待什么，從而針對這些信息對數據庫的性能進行調整。
COL USERNAME FOR A12;
COL PROGRAM? FOR A32;
COL EVENT??? FOR A26;
SELECT S.USERNAME
????? ,S.PROGRAM
????? ,S.STATUS
????? ,SE.EVENT
????? ,SE.TOTAL_WAITS
????? ,SE.TOTAL_TIMEOUTS
????? ,SE.TIME_WAITED
????? ,SE.AVERAGE_WAIT
FROM V$SESSIONS,V$SESSION_EVENT SE
WHERE S.SID=SE.SID AND SE.EVENT NOT?LIKE?'SQLNet%’
? AND S.STATUS =‘ACTIVE’?AND S.USERNAME IS?NOT?NULL;
?
2.ORACLE中查詢被鎖的表并釋放session
SELECT A.OWNER
? ,A.OBJECT_NAME
? ,B.XIDUSN
? ,B.XIDSLOT
? ,B.XIDSQN
? ,B.SESSION_ID
? ,B.ORACLE_USERNAME
? ,B.OS_USER_NAME
? ,B.PROCESS
? ,B.LOCKED_MODE
? ,C.MACHINE
? ,C.STATUS
? ,C.SERVER
? ,C.SID
? ,C.SERIAL#
? ,C.PROGRAM
FROM ALL_OBJECTS A,VKaTeX parse error: Expected group after '_' at position 23: …OBJECT B,SYS.GV_?SESSION C
WHERE? A.OBJECT_ID = B.OBJECT_ID? AND B.PROCESS = C.PROCESS? ORDER?BY 1,2;
?
3.查看占用系統IO較大的session
SELECT se.sid
????? ,se.serial#
????? ,pr.spid
????? ,se.username
????? ,se.status
????? ,se.terminal
????? ,se.program
????? ,se.module
????? ,se.sql_address
????? ,st.event
????? ,st.p1text
????? ,si.physical_reads
????? ,si.block_changes
FROM v$sessionse,v$session_wait st,v$ses{s}_{i}osi,v$process pr
WHERE st.sid=se.sid? AND st.sid=si.sid
? AND se.paddr=pr.ADDR AND se.sid>6
? AND st.wait_time=0 AND st.event NOT?LIKE?‘%SQL%’?
? ORDER?BY physical_reads DESC;
?
4.找出耗cpu較多的session
select a.sid
????? ,spid
????? ,status
????? ,substr(a.program,1,40) prog
????? ,a.terminal
????? ,osuser
????? ,value/60/100 value
from v$sessiona,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr
?? order?by?value desc