数据库维护常用SQL语句集合.docx
文本预览下载声明
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.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查看对应连接正在运行的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 Start_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_Name,
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 = 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 Activity_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.O
显示全部