Oracle数据库维护常用SQL语句集合

7a[,n\g6d+suD
q01、 求当前会话的SID，SERIAL#         
3fyq*?
[ ^0SELECT Sid, Serial# FROM V$session WHERE Audsid = Sys_Context('USERENV', 'SESSIONID');

2、 查询session的OS进程ID         51Testing软件测试网v4Z7g)d?&n@%@
SELECT p.Spid "OS Thread", b.NAME "Name-User", s.Program, s.Sid, s.Serial#,51Testing软件测试网0H*]-}BB {$[o
s.Osuser, s.Machine
Of1jM j0FROM V$process p, V$session s, V$bgprocess b51Testing软件测试网P7RX/JRTg%b{
WHERE p.Addr = s.Paddr
o(fR5e-pv.UNU8c0AND p.Addr = b.Paddr51Testing软件测试网c E `m2_x JNx Y
And (s.sid=&1 or p.spid=&1)
Z2u1zq
sfr0UNION ALL51Testing软件测试网mPc].n(p#J7Sn
SELECT p.Spid "OS Thread", s.Username "Name-User", s.Program, s.Sid,51Testing软件测试网+Y|2n%JF'[q
s.Serial#, s.Osuser, s.Machine
#~|YF ~1x6p-Ga:i0FROM V$process p, V$session s
8Xv2e pq'xn0WHERE p.Addr = s.Paddr
i'TDdM#|+^k2w p0And (s.sid=&1 or p.spid=&1)
$\$pJ*I{1{0AND s.Username IS NOT NULL;51Testing软件测试网2a(a0t'v*m Lv-Sw

3、根据sid查看对应连接正在运行的sql         
M&tb5fJ0SELECT /*+ PUSH_SUBQ */
)hw)EX t%V(q0Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,51Testing软件测试网$k0W Q!Owt
Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,
F!J[+a1XlV3e0Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls,51Testing软件测试网(r'eG8xz
Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time,51Testing软件测试网\/J@6?&d#wxzr
SYSDATE Finish_Time, '>' || Address Sql_Address, 'N' Status51Testing软件测试网7A|"S+@"cC7T.L\
FROM V$sqlarea51Testing软件测试网C^e0ZK+C8N]9a0p5t#?
WHERE Address = (SELECT Sql_Address51Testing软件测试网
@1EK)xno WYY
FROM V$session51Testing软件测试网p0l[sk0^&Ri3c
WHERE Sid = &sid );

du1{/O%tC ^)?\s04、查找object为哪些进程所用         
!d7BZ/G*| y&{N0SELECT p.Spid, s.Sid, s.Serial# Serial_Num, s.Username User_Name,
0Gf0O8_Eif/Gws.L0a.TYPE Object_Type, s.Osuser Os_User_Name, a.Owner,51Testing软件测试网}"_z&p
glK4kB&SL.b
a.OBJECT Object_Name,51Testing软件测试网 ZYkJr-Q^
Decode(Sign(48 - Command), 1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,
"H j5l'_m4}/?0p.Program Oracle_Process, s.Terminal Terminal, s.Program Program,51Testing软件测试网+f0S T5R4S,dm?S~i
s.Status Session_Status51Testing软件测试网#tsi(~V p:L
FROM V$session s, V$access a, V$process p
s'X6X;^&OC$~^%F"E0WHERE s.Paddr = p.Addr
z1IC;yX!?b+gD-r3q0AND s.TYPE = 'USER'51Testing软件测试网1^2XVR1m/Ez;J-oR@1eck
AND a.Sid = s.Sid51Testing软件测试网0K$o)V,C8_"C
AND a.OBJECT = '&obj'51Testing软件测试网?2u4_6?$XF
{
ORDER BY s.Username, s.Osuser51Testing软件测试网$W"xQX@1W3Z^e"w

:yla7DiF7lA O05、查看有哪些用户连接         51Testing软件测试网p}TY#G4ID
SELECT s.Osuser Os_User_Name,
+~~9DnM-j D0Decode(Sign(48 - Command),1,To_Char(Command),51Testing软件测试网/k0}o3adB,X5m
'Action Code #' || To_Char(Command)) Action,
7{O?ze'}.WA9r ~4M0p.Program Oracle_Process, Status Session_Status, s.Terminal Terminal,51Testing软件测试网~!t/_pu eJ ?
s.Program Program, s.Username User_Name,
a0Fo!iw0q0s.Fixed_Table_Sequence Activity_Meter, '' Query, 0 Memory,
i8D#Os0Zq2N@:{s}I00 Max_Memory, 0 Cpu_Usage, s.Sid, s.Serial# Serial_Num
/a0oT;o[o|X|0FROM V$session s, V$process p
O6_+J(B"~a \\6J\:n0WHERE s.Paddr = p.Addr51Testing软件测试网vLe-c/c8eN/p
AND s.TYPE = 'USER'51Testing软件测试网 g,z&_nK)Xsj5Z
ORDER BY s.Username, s.Osuser51Testing软件测试网:^T!qd
tr
CA$A

5T"h A j6t"D-h06、根据v.sid查看对应连接的资源占用等情况         
3M3f(]HP3v6~q+O"^0SELECT n.NAME, v.VALUE, n.CLASS, n.Statistic#51Testing软件测试网2n5t+T'x y%k8V#l6xr
FROM V$statname n, V$sesstat v
c7J-REn0WHERE v.Sid = &sid
O3d] p_0AND v.Statistic# = n.Statistic#
GH} Z7t2f2JDz0[0ORDER BY n.CLASS, n.Statistic#51Testing软件测试网o#c8WQk6C

7、查询耗资源的进程（top session）         
c5G pe-^P0xSOl*A5G0SELECT s.Schemaname Schema_Name,
x
qCPH;C0Decode(Sign(48 - Command),
eg:@$H0pT'b4b01, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,
P-{ Z`g$SH{0Status Session_Status, s.Osuser Os_User_Name, s.Sid, p.Spid,
p
a(Y.K V Y#^|'s0s.Serial# Serial_Num, Nvl(s.Username, '[Oracle process]') User_Name,51Testing软件测试网,S
tJS#w`
tnO
s.Terminal Terminal, s.Program Program, St.VALUE Criteria_Value51Testing软件测试网Lo te r
FROM V$sesstat St, V$session s, V$process p51Testing软件测试网;Pa1mB CF1j$O
WHERE St.Sid = s.Sid
z*q|5AO*q0AND St.Statistic# = To_Number('38')51Testing软件测试网fJ7eh
F2z'P D*R
AND ('ALL' = 'ALL' OR s.Status = 'ALL')
:so^!l1l e&b0AND p.Addr = s.Paddr
m;iMgH_Y0ORDER BY St.VALUE DESC, p.Spid ASC, s.Username ASC, s.Osuser ASC

8、查看锁（lock）情况         51Testing软件测试网pg3li3C'\A{{
SELECT /*+ RULE */51Testing软件测试网Y j`K1I/~yK
Ls.Osuser Os_User_Name, Ls.Username User_Name,51Testing软件测试网F/bRw8F4Q
Decode(Ls.TYPE,51Testing软件测试网#@G z-yAF/Y6m#^v4z
'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock',
O'b4Q[X~0'TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') Lock_Type,
q6vAG[8~
t/lM0o.Object_Name OBJECT,51Testing软件测试网`$y*^"E#a c0DP g
Decode(Ls.Lmode,51Testing软件测试网*mQOec8I"Z!@
1, NULL, 2, 'Row Share', 3, 'Row Exclusive',51Testing软件测试网QghWf WI
4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive',51Testing软件测试网 Ec(kC$G%U"u$e9z
NULL) Lock_Mode,
j)e:Cnc z0`0o.Owner, Ls.Sid, Ls.Serial# Serial_Num, Ls.Id1, Ls.Id2
+c"{ _9{1?8y3I9O f0FROM Sys.Dba_Objects o,
,U
fh N,PjK0(SELECT s.Osuser, s.Username, l.TYPE, l.Lmode, s.Sid, s.Serial#, l.Id1,
*B_ dF)R)Rt9R7a0l.Id2
4hp*@rA rx*G"{+~0FROM V$session s, V$lock l
Xa|,S@o0WHERE s.Sid = l.Sid) Ls51Testing软件测试网$OuZc)[S
WHERE o.Object_Id = Ls.Id1
:N C?v5kg_0AND o.Owner <> 'SYS'51Testing软件测试网$If1V"G4R(e0r-z
ORDER BY o.Owner, o.Object_Name

9、查看等待（wait）情况51Testing软件测试网9\E#LX*~IJ}Z'r
SELECT Ws.CLASS, Ws.COUNT COUNT, SUM(Ss.VALUE) Sum_Value
6T;b8w%s4}*Xclh0FROM V$waitstat Ws, V$sysstat Ss51Testing软件测试网L4E4?.}W
WHERE Ss.NAME IN ('db block gets', 'consistent gets')
qK-i-wn1at0GROUP BY Ws.CLASS, Ws.COUNT51Testing软件测试网;uDiX yK,{:fp8Z

\__u/j `.BZ010、求process/session的状态         
w7w9Jr+e!q0SELECT p.Pid, p.Spid, s.Program, s.Sid, s.Serial#51Testing软件测试网,A!Lj kC)@A;qU']e
FROM V$process p, V$session s51Testing软件测试网-bfC4Ml1N
WHERE s.Paddr = p.Addr;

11、求谁阻塞了某个session(10g)         
Ut%Z;~/ri wm0SELECT Sid, Username, Event, Blocking_Session, Seconds_In_Wait, Wait_Time
J5_'B:t&g4@0FROM V$session51Testing软件测试网 O)Eb
?t2NfUgj
WHERE State IN ('WAITING')
3Fn\kK i.S]
f0AND Wait_Class != 'Idle';51Testing软件测试网_\6~dHyF y

12、查会话的阻塞         51Testing软件测试网TSga9P K!o
col user_name format a3251Testing软件测试网:A8_ @'o1Q0j6jD
SELECT /*+ rule */
6k:mUtTMW:I H0Lpad(' ', Decode(l.Xidusn, 0, 3, 0)) || l.Oracle_Username User_Name,
EmhN:V|
K:T0o.Owner, o.Object_Name, s.Sid, s.Serial#51Testing软件测试网G*BAQf*jy0STE
FROM V$locked_Object l, Dba_Objects o, V$session s51Testing软件测试网hm:e^;V M m:B&x
WHERE l.Object_Id = o.Object_Id51Testing软件测试网?EWU7JFW
AND l.Session_Id = s.Sid
]av6Gh$P0ORDER BY o.Object_Id, Xidusn DESC;
+kK[:]'Ca A l0col username format a15
x8g `J1P xSPW0col lock_level format a851Testing软件测试网jT3QS6P3{
col owner format a18
Z(z&I!w1ACf'p?0col object_name format a3251Testing软件测试网)e1l8vDd'j/S
SELECT /*+ rule */
b9ESo"{%m`el0s.Username,51Testing软件测试网!W/ip5i u
Decode(l.TYPE, 'tm', 'table lock', 'tx', 'row lock', NULL) Lock_Level,51Testing软件测试网`h]WDC [
o.Owner, o.Object_Name, s.Sid, s.Serial#51Testing软件测试网7DK["{nb nh
FROM V$session s, V$lock l, Dba_Objects o51Testing软件测试网]f3p h!Y k
WHERE l.Sid = s.Sid
w+z{Mfu;RNMS0AND l.Id1 = o.Object_Id(+)51Testing软件测试网3C:[Iz+q w3R?a6E8z
AND s.Username IS NOT NULL;

13、求等待的事件及会话信息/求会话的等待及会话信息         51Testing软件测试网j#[8e0jOEd~4E;t
SELECT Se.Sid, s.Username, Se.Event, Se.Total_Waits, Se.Time_Waited,
#du a&m!?0R_}-fu0Se.Average_Wait
iw$O;Z2S
m*Z3hx0FROM V$session s, V$session_Event Se
I0l/Zi Nfo0WHERE s.Username IS NOT NULL51Testing软件测试网.P1C2|hz
AND Se.Sid = s.Sid
M6^E(w{P}B0AND s.Status = 'ACTIVE'51Testing软件测试网@y\)NHh#nx-T5z}6h
AND Se.Event NOT LIKE '%SQL*Net%'
z bZ2[
wCh/lU0ORDER BY s.Username;51Testing软件测试网I g N4P/N*}
SELECT s.Sid, s.Username, Sw.Event, Sw.Wait_Time, Sw.State,51Testing软件测试网B'YX
cC
Sw.Seconds_In_Wait
a/c7cQI0FROM V$session s, V$session_Wait Sw
a4aL(\,HP3G&e.Q1v0WHERE s.Username IS NOT NULL
,ZU!D&SA+T0AND Sw.Sid = s.Sid51Testing软件测试网7d^ M2T-UD
AND Sw.Event NOT LIKE '%SQL*Net%'
j O|YRMH7Vp0ORDER BY s.Username;

~/MH}3t N^{014、求会话等待的file_id/block_id         
Z:Al[b/k7F0col event format a24
WW;NxQ$FI+u-YK0col p1text format a1251Testing软件测试网/gW3\{s5E.I)G$P7~ o]
col p2text format a1251Testing软件测试网6jgb&zv{;P-r
col p3text format a1251Testing软件测试网U m@5\{s?
SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P351Testing软件测试网j1[6np jw
FROM V$session_Wait51Testing软件测试网6l n^ J S.gy6T
SH
WHERE Event NOT LIKE '%SQL%'
p!lx0emIHo0AND Event NOT LIKE '%rdbms%'51Testing软件测试网D;iwf(V!}'h
AND Event NOT LIKE '%mon%'51Testing软件测试网w3Qp,]y-xb
ORDER BY Event;51Testing软件测试网gR-Y$a&o'Vg*v!P_ al
X
SELECT NAME, Wait_Time
QUa)~OJ0FROM V$latch l51Testing软件测试网QRV7b3z9|
WHERE EXISTS (SELECT 151Testing软件测试网1B&GHmjPzkw-\Z
FROM (SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P351Testing软件测试网*fV#oo:ez;`
FROM V$session_Wait51Testing软件测试网*?qU%XI/`Y*I
WHERE Event NOT LIKE '%SQL%'
a%Q h'V(IQ ^ok0AND Event NOT LIKE '%rdbms%'
j:Ay)p[)T+x*J0L+C/A0AND Event NOT LIKE '%mon%') x51Testing软件测试网X&M7KsF#F
WHERE x.P1 = l.Latch#);

15、求会话等待的对象         51Testing软件测试网\P;U[I8i4wY8k
col owner format a1851Testing软件测试网O#A7p9F)w iv
col segment_name format a32
$d c[0{ |(XVa3o;\0col segment_type format a3251Testing软件测试网bi)E"y$r$m&w2e
SELECT Owner, Segment_Name, Segment_Type51Testing软件测试网FD*P%w1oU
FROM Dba_Extents51Testing软件测试网G(V |U4L2Y*},L
WHERE File_Id = &File_Id
8p\i``-z2pF0AND &Block_Id BETWEEN Block_Id AND Block_Id + Blocks - 1;

|-@*xW3k^016、求出某个进程，并对它进行跟踪         51Testing软件测试网%Rn~ v'}Q)Y
SELECT s.Sid, s.Serial#
5kU Pwt
q\5|0FROM V$session s, V$process p51Testing软件测试网Hy3p5N.g
WHERE s.Paddr = p.Addr
E5se,z"g%?2o5C:bP0AND p.Spid = &1;
6P#B
|#^+?k$X,O}7|@0Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, TRUE);
Fa5{ pWuyV0Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, FALSE);

%G_5\h?/Rj*Ae j$Ps017、求当前session的跟踪文件         51Testing软件测试网Z*zXf8m/r\'?
SELECT P1.VALUE || '/' || P2.VALUE || '_ora_' || p.Spid || '.ora' Filename51Testing软件测试网*Tl
hY0[
FROM V$process p, V$session s, V$parameter P1, V$parameter P2
G&e0_ww'a0WHERE P1.NAME = 'user_dump_dest'
%u7}7ziGh0AND P2.NAME = 'instance_name'
m'LK0r+q0N+?z0AND p.Addr = s.Paddr
8}M e|y(I'N!Q0AND s.Audsid = Userenv('SESSIONID')51Testing软件测试网ba:ZH\vG.Q
AND p.Background IS NULL51Testing软件测试网m(yB4Q,n K/@
AND Instr(p.Program, 'CJQ') = 0;

F5H)UK$OI%o,?9S_;Em018、求出锁定的对象        51Testing软件测试网d:}.Rp'Z9V"pY
SELECT Do.Object_Name, Session_Id, Process, Locked_Mode51Testing软件测试网Zu@q3f#m
FROM V$locked_Object Lo, Dba_Objects Do51Testing软件测试网%YLg
lp
WHERE Lo.Object_Id = Do.Object_Id;