我常用的幾個SQL，不斷新增中

3、檢視回滾段狀態
SELECT a.segment_name,b.status
FROM Dba_Rollback_Segs a,
v$rollstat b
WHERE a.segment_id=b.usn
ORDER BY 2

4、檢視哪些session正在使用哪些回滾段
/*col 回滾段名 format a10
col SID format 9990
col 使用者名稱 format a10
col 操作程式 format a80
col status format a6 trunc*/

SELECT r.name 回滾段名,
s.sid,
s.serial#,
s.username 使用者名稱,
s.status,
s.SQL_ADDRESS,
t.cr_get,
t.phy_io,
t.used_ublk,
t.noundo,
substr(s.program, 1, 78) 操作程式
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
WHERE t.addr = s.taddr and t.xidusn = r.usn
-- AND r.NAME IN ('ZHYZ_RBS')
ORDER BY t.cr_get,t.phy_io

5、檢視無法擴充套件的段
SELECT segment_name,
segment_type,
owner,
a.tablespace_name "tablespacename",
initial_extent/1024 "inital_extent(K)",
next_extent/1024 "next_extent(K)",
pct_increase,
b.bytes/1024 "tablespace max free space(K)",
b.sum_bytes/1024 "tablespace total free space(K)"
FROM dba_segments a,
(SELECT tablespace_name,MAX(bytes) bytes,SUM(bytes) sum_bytes FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
AND next_extent>b.bytes
ORDER BY 4,3,1

6、檢視系統鎖
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,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID )
AND (B.PROCESS = C.PROCESS )
-- AND
ORDER BY 1,2 ;

7、檢視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,
st.p1,
st.p2,
st.p3,
st.STATE,
st.SECONDS_IN_WAIT,
si.physical_reads,
si.block_changes
FROM v$session se,
v$session_wait st,
v$sess_io si,
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

8、檢視正在使用臨時段的session
SELECT username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr

9、檢視某一個作業系統程式的SQL
SELECT a.username,
a.machine,
a.program,
a.sid,
a.serial#,
a.status,
c.piece,
c.sql_text
FROM v$session a,
v$process b,
v$sqltext c
WHERE b.spid=****08
AND b.addr=a.paddr
AND a.sql_address=c.address(+)
ORDER BY c.piece

10、檢視enqueue鎖狀態
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid
sess, id1, id2, lmode, request,
type FROM
V$LOCK WHERE (id1, id2, type)
IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
;

10、找出enqueue等待的holder程式
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid
sess, id1, id2, lmode, request,
type FROM
V$LOCK WHERE (id1, id2, type)
IN (SELECT id1, id2, type FROM V$LOCK WHERErequest>0)
ORDER BY id1, request
;

11、檢視hotblock
SELECT /*+ ordered */
E.OWNER || '.' || E.SEGMENT_NAME SEGMENT_NAME,
E.EXTENT_ID EXTENT#,
X.DBABLK - E.BLOCK_ID + 1 BLOCK#,
X.TCH,
L.CHILD#
FROM SYS.V$LATCH_CHILDREN L, SYS.X$BH X, SYS.DBA_EXTENTS E
WHERE L.NAME = 'cache buffers chains'
AND L.SLEEPS > &SLEEP_COUNT
AND X.HLADDR = L.ADDR
AND E.FILE_ID = X.FILE#
AND X.DBABLK BETWEEN E.BLOCK_ID AND E.BLOCK_ID + E.BLOCKS - 1
ORDER BY X.TCH;