Decode(Ls.Lmode,

　　1, NULL, 2, 'Row Share', 3, 'Row Exclusive',

　　4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive',

　　NULL) Lock_Mode,

　　o.Owner, Ls.Sid, Ls.Serial# Serial_Num, Ls.Id1, Ls.Id2

　　FROM Sys.Dba_Objects o,

　　(SELECT s.Osuser, s.Username, l.TYPE, l.Lmode, s.Sid, s.Serial#, l.Id1,

　　l.Id2

　　FROM V$session s, V$lock l

　　WHERE s.Sid = l.Sid) Ls

　　WHERE o.Object_Id = Ls.Id1

　　AND o.Owner <> 'SYS'

　　ORDER BY o.Owner, o.Object_Name

　　9、查看等待(wait)情况

　　SELECT Ws.CLASS, Ws.COUNT COUNT, SUM(Ss.VALUE) Sum_Value

　　FROM V$waitstat Ws, V$sysstat Ss

　　WHERE Ss.NAME IN ('db block gets', 'consistent gets')

　　GROUP BY Ws.CLASS, Ws.COUNT

　　10、求process/session的状态

　　SELECT p.Pid, p.Spid, s.Program, s.Sid, s.Serial#

　　FROM V$process p, V$session s

　　WHERE s.Paddr = p.Addr;

　　11、求谁阻塞了某个session(10g)

　　SELECT Sid, Username, Event, Blocking_Session, Seconds_In_Wait, Wait_Time

　　FROM V$session

　　WHERE State IN ('WAITING')

　　AND Wait_Class != 'Idle';

　　12、查会话的阻塞

　　col user_name format a32

　　SELECT /*+ rule */

　　Lpad(' ', Decode(l.Xidusn, 0, 3, 0)) || l.Oracle_Username User_Name,

　　o.Owner, o.Object_Name, s.Sid, s.Serial#

　　FROM V$locked_Object l, Dba_Objects o, V$session s

　　WHERE l.Object_Id = o.Object_Id

　　AND l.Session_Id = s.Sid

　　ORDER BY o.Object_Id, Xidusn DESC;

　　col username format a15

　　col lock_level format a8

　　col owner format a18

　　col object_name format a32

　　SELECT /*+ rule */

　　s.Username,

　　Decode(l.TYPE, 'tm', 'table lock', 'tx', 'row lock', NULL) Lock_Level,

　　o.Owner, o.Object_Name, s.Sid, s.Serial#

　　FROM V$session s, V$lock l, Dba_Objects o

　　WHERE l.Sid = s.Sid

　　AND l.Id1 = o.Object_Id(+)

　　AND s.Username IS NOT NULL;

　　13、求等待的事件及会话信息/求会话的等待及会话信息

　　SELECT Se.Sid, s.Username, Se.Event, Se.Total_Waits, Se.Time_Waited,

　　Se.Average_Wait

　　FROM V$session s, V$session_Event Se

　　WHERE s.Username IS NOT NULL

　　AND Se.Sid = s.Sid

　　AND s.Status = 'ACTIVE'

　　AND Se.Event NOT LIKE '%SQL*Net%'

　　ORDER BY s.Username;

　　SELECT s.Sid, s.Username, Sw.Event, Sw.Wait_Time, Sw.State,

　　Sw.Seconds_In_Wait

　　FROM V$session s, V$session_Wait Sw

　　WHERE s.Username IS NOT NULL

　　AND Sw.Sid = s.Sid

　　AND Sw.Event NOT LIKE '%SQL*Net%'

　　ORDER BY s.Username;

[1] [2] [3] [4]