《oracle数据库维护常用sql语句集合》由会员分享，可在线阅读，更多相关《oracle数据库维护常用sql语句集合（9页珍藏版）》请在金锄头文库上搜索。
1、1、 求当前会话的SID，SERIAL# SELECT Sid, Serial# FROM V$session WHERE Audsid = Sys_Context(USERENV, SESSIONID); 2、 查询session的OS进程ID SELECT p.Spid OS Thread, b.NAME Name-User, s.Program, s.Sid, s.Serial#, s.Osuser, s.Machine FROM V$process p, V$session s, V$bgprocess b WHERE p.Addr = s.Paddr AND p.Addr = b.P
2、addr And (s.sid=&1 or p.spid=&1) UNION ALL SELECT p.Spid OS Thread, s.Username Name-User, s.Program, s.Sid, s.Serial#, s.Osuser, s.Machine FROM V$process p, V$session s WHERE p.Addr = s.Paddr And (s.sid=&1 or p.spid=&1) AND s.Username IS NOT NULL; 3、根据sid查看对应连接正在运行的sql SELECT /*+ PUSH_SUBQ */ Comman
3、d_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts, Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions, Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls, Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time, SYSDATE Finish_Time, | Addre
4、ss Sql_Address, N Status FROM V$sqlarea WHERE Address = (SELECT Sql_Address FROM V$session WHERE Sid = &sid ); 4、查找object为哪些进程所用 SELECT p.Spid, s.Sid, s.Serial# Serial_Num, s.Username User_Name, a.TYPE Object_Type, s.Osuser Os_User_Name, a.Owner, a.OBJECT Object_Name, Decode(Sign(48 - Command), 1, T
5、o_Char(Command), Action Code # | To_Char(Command) Action, p.Program Oracle_Process, s.Terminal Terminal, s.Program Program, s.Status Session_Status FROM V$session s, V$access a, V$process p WHERE s.Paddr = p.Addr AND s.TYPE = USER AND a.Sid = s.Sid AND a.OBJECT = &obj ORDER BY s.Username, s.Osuser 5
6、、查看有哪些用户连接 SELECT s.Osuser Os_User_Name, Decode(Sign(48 - Command),1,To_Char(Command), Action Code # | To_Char(Command) Action, p.Program Oracle_Process, Status Session_Status, s.Terminal Terminal, s.Program Program, s.Username User_Name, s.Fixed_Table_Sequence Activity_Meter, Query, 0 Memory, 0 Max
7、_Memory, 0 Cpu_Usage, s.Sid, s.Serial# Serial_Num FROM V$session s, V$process p WHERE s.Paddr = p.Addr AND s.TYPE = USER ORDER BY s.Username, s.Osuser 6、根据v.sid查看对应连接的资源占用等情况 SELECT n.NAME, v.VALUE, n.CLASS, n.Statistic# FROM V$statname n, V$sesstat v WHERE v.Sid = &sid AND v.Statistic# = n.Statisti
8、c# ORDER BY n.CLASS, n.Statistic# 7、查询耗资源的进程（top session） SELECT s.Schemaname Schema_Name, Decode(Sign(48 - Command), 1, To_Char(Command), Action Code # | To_Char(Command) Action, Status Session_Status, s.Osuser Os_User_Name, s.Sid, p.Spid, s.Serial# Serial_Num, Nvl(s.Username, Oracle process) User_
9、Name, s.Terminal Terminal, s.Program Program, St.VALUE Criteria_Value FROM V$sesstat St, V$session s, V$process p WHERE St.Sid = s.Sid AND St.Statistic# = To_Number(38) AND (ALL = ALL OR s.Status = ALL) AND p.Addr = s.Paddr ORDER BY St.VALUE DESC, p.Spid ASC, s.Username ASC, s.Osuser ASC 8、查看锁（lock）
10、情况 SELECT /*+ RULE */ Ls.Osuser Os_User_Name, Ls.Username User_Name, Decode(Ls.TYPE, RW, Row wait enqueue lock, TM, DML enqueue lock, TX, Transaction enqueue lock, UL, User supplied lock) Lock_Type, o.Object_Name OBJECT, Decode(Ls.Lmode, 1, NULL, 2, Row Share, 3, Row Exclusive, 4, Share, 5, Share Ro
11、w 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
12、 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,
13、 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_Use
14、rname 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 SE
15、LECT /*+ 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
