Oracle数据库维护常用的SQL代码示例.doc

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