资源描述:
《oracle数据库维护常用sql语句集合》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、1、求当前会话的SID,SERIAL#SELECTSid,Serial#FROMV$sessionWHEREAudsid=Sys_Context('USERENV','SESSIONID');2、查询session的OS进程IDSELECTp.Spid"OSThread",b.NAME"Name-User",s.Program,s.Sid,s.Serial#,s.Osuser,s.MachineFROMV$processp,V$sessions,V$bgprocessbWHEREp.Addr=s.Pad
2、drANDp.Addr=b.PaddrAnd(s.sid=&1orp.spid=&1)UNIONALLSELECTp.Spid"OSThread",s.Username"Name-User",s.Program,s.Sid,s.Serial#,s.Osuser,s.MachineFROMV$processp,V$sessionsWHEREp.Addr=s.PaddrAnd(s.sid=&1orp.spid=&1)ANDs.UsernameISNOTNULL;3、根据sid查看对应连接正在运行的sqlSE
3、LECT/*+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_Process
4、ed,SYSDATEStart_Time,SYSDATEFinish_Time,'>'
5、
6、AddressSql_Address,'N'StatusFROMV$sqlareaWHEREAddress=(SELECTSql_AddressFROMV$sessionWHERESid=&sid);4、查找object为哪些进程所用SELECTp.Spid,s.Sid,s.Serial#Serial_Num,s.UsernameUser_Name,a.TYPEObject_Type,s.OsuserOs_User
7、_Name,a.Owner,a.OBJECTObject_Name,Decode(Sign(48-Command),1,To_Char(Command),'ActionCode#'
8、
9、To_Char(Command))Action,p.ProgramOracle_Process,s.TerminalTerminal,s.ProgramProgram,s.StatusSession_StatusFROMV$sessions,V$accessa,V$processpWHEREs.Paddr=p.AddrAN
10、Ds.TYPE='USER'ANDa.Sid=s.SidANDa.OBJECT='&obj'ORDERBYs.Username,s.Osuser5、查看有哪些用户连接SELECTs.OsuserOs_User_Name,Decode(Sign(48-Command),1,To_Char(Command),'ActionCode#'
11、
12、To_Char(Command))Action,p.ProgramOracle_Process,StatusSession_Status,s.TerminalTermina
13、l,s.ProgramProgram,s.UsernameUser_Name,s.Fixed_Table_SequenceActivity_Meter,''Query,0Memory,0Max_Memory,0Cpu_Usage,s.Sid,s.Serial#Serial_NumFROMV$sessions,V$processpWHEREs.Paddr=p.AddrANDs.TYPE='USER'ORDERBYs.Username,s.Osuser6、根据v.sid查看对应连接的资源占用等情况SELEC
14、Tn.NAME,v.VALUE,n.CLASS,n.Statistic#FROMV$statnamen,V$sesstatvWHEREv.Sid=&sidANDv.Statistic#=n.Statistic#ORDERBYn.CLASS,n.Statistic#7、查询耗资源的进程(topsession)SELECTs.SchemanameSchema_Name,Decode(Sign(48-Command),1,To_Char(Comm