oracle数据库维护常用sql语句集合

《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