维护常用SQL语句.docx

1、维护常用SQL语句Oracle数据库维护常用SQL语句集合-进程相关： 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 WHE

2、RE p.Addr = s.Paddr AND p.Addr = b.Paddr 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查看对应连接正在运

3、行的sql SELECT /*+ PUSH_SUBQ */ Command_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 Star

4、t_Time, SYSDATE Finish_Time, | Address 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_N

5、ame, Decode(Sign(48 - Command), 1, To_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 =

6、&obj ORDER BY s.Username, s.Osuser 5、查看有哪些用户连接 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 Ac

7、tivity_Meter, Query, 0 Memory, 0 Max_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

8、= &sid AND v.Statistic# = n.Statistic# 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, N

9、vl(s.Username, Oracle process) User_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.Us

10、ername ASC, s.Osuser ASC 8、查看锁（lock）情况 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,

11、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.Obje

12、ct_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

13、, 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( , Deco

14、de(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 form

15、at 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、求等待的事件及会话信息

16、/求会话的等待及会话信息 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.Wai

17、t_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; 14、求会话等待的file_id/block_id col event format a24 col p1text format a12 col p2text format a12 col p3text format a12 SELECT Sid, E

18、vent, P1text, P1, P2text, P2, P3text, P3 FROM V$session_Wait WHERE Event NOT LIKE %SQL% AND Event NOT LIKE %rdbms% AND Event NOT LIKE %mon% ORDER BY Event; SELECT NAME, Wait_Time FROM V$latch l WHERE EXISTS (SELECT 1 FROM (SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3 FROM V$session_Wait WHE

19、RE Event NOT LIKE %SQL% AND Event NOT LIKE %rdbms% AND Event NOT LIKE %mon%) x WHERE x.P1 = l.Latch#); 15、求会话等待的对象 col owner format a18 col segment_name format a32 col segment_type format a32 SELECT Owner, Segment_Name, Segment_Type FROM Dba_Extents WHERE File_Id = &File_Id AND &Block_Id BETWEEN Blo

20、ck_Id AND Block_Id + Blocks - 1; 16、求出某个进程，并对它进行跟踪 SELECT s.Sid, s.Serial# FROM V$session s, V$process p WHERE s.Paddr = p.Addr AND p.Spid = &1; Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, TRUE); Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, FALSE); 17、求当前session的跟踪文件 SELECT P1.VALUE | / |

21、 P2.VALUE | _ora_ | p.Spid | .ora Filename FROM V$process p, V$session s, V$parameter P1, V$parameter P2 WHERE P1.NAME = user_dump_dest AND P2.NAME = instance_name AND p.Addr = s.Paddr AND s.Audsid = Userenv(SESSIONID) AND p.Background IS NULL AND Instr(p.Program, CJQ) = 0; 18、求出锁定的对象 SELECT Do.Object_Name, Session_Id, Process, Locked_Mode FROM V$locked_Object Lo, Dba_Objects Do WHERE Lo.Object_Id = Do.Object_Id; - 资料引用: