《oracle数据库维护常用的sql代码示例》由会员分享，可在线阅读，更多相关《oracle数据库维护常用的sql代码示例（5页珍藏版）》请在金锄头文库上搜索。
1、1、求当前会话的SID，SERIAL#1. SELECTSid,Serial# 2. FROMV$session 3. WHEREAudsid=Sys_Context(USERENV,SESSIONID);2、查询session的OS进程ID1. SELECTp.SpidOSThread,b.NAMEName-User,s.Program,s.Sid,s.Serial#, 2. s.Osuser,s.Machine 3. FROMV$processp,V$sessions,V$bgprocessb 4. WHEREp.Addr=s.Paddr 5. ANDp.Addr=b.Paddr 6. A
2、nd(s.sid=&1orp.spid=&1) 7. UNIONALL 8. SELECTp.SpidOSThread,s.UsernameName-User,s.Program,s.Sid, 9. s.Serial#,s.Osuser,s.Machine 10. FROMV$processp,V$sessions 11. WHEREp.Addr=s.Paddr 12. And(s.sid=&1orp.spid=&1) 13. ANDs.UsernameISNOTNULL;3、根据sid查看对应连接正在运行的sql1. SELECT/*+PUSH_SUBQ*/ 2. Command_Type,
3、Sql_Text,Sharable_Mem,Persistent_Mem,Runtime_Mem,Sorts, 3. Version_Count,Loaded_Versions,Open_Versions,Users_Opening,Executions, 4. Users_Executing,Loads,First_Load_Time,Invalidations,Parse_Calls, 5. Disk_Reads,Buffer_Gets,Rows_Processed,SYSDATEStart_Time, 6. SYSDATEFinish_Time,|AddressSql_Address,N
4、Status 7. FROMV$sqlarea 8. WHEREAddress=(SELECTSql_Address 9. FROMV$session 10. WHERESid=&sid);4、查找object为哪些进程所用1. SELECTp.Spid,s.Sid,s.Serial#Serial_Num,s.UsernameUser_Name, 2. a.TYPEObject_Type,s.OsuserOs_User_Name,a.Owner, 3. a.OBJECTObject_Name, 4. Decode(Sign(48-Command),1,To_Char(Command),Acti
5、onCode#|To_Char(Command)Action, 5. p.ProgramOracle_Process,s.TerminalTerminal,s.ProgramProgram, 6. s.StatusSession_Status 7. FROMV$sessions,V$accessa,V$processp 8. WHEREs.Paddr=p.Addr 9. ANDs.TYPE=USER10. ANDa.Sid=s.Sid 11. ANDa.OBJECT=&obj12. ORDERBYs.Username,s.Osuser5、查看有哪些用户连接1. SELECTs.OsuserOs
6、_User_Name, 2. Decode(Sign(48-Command),1,To_Char(Command), 3. ActionCode#|To_Char(Command)Action, 4. p.ProgramOracle_Process,StatusSession_Status,s.TerminalTerminal, 5. s.ProgramProgram,s.UsernameUser_Name, 6. s.Fixed_Table_SequenceActivity_Meter,Query,0Memory, 7. 0Max_Memory,0Cpu_Usage,s.Sid,s.Seri
7、al#Serial_Num 8. FROMV$sessions,V$processp 9. WHEREs.Paddr=p.Addr 10. ANDs.TYPE=USER11. ORDERBYs.Username,s.Osuser6、根据v.sid查看对应连接的资源占用等情况1. SELECTn.NAME,v.VALUE,n.CLASS,n.Statistic# 2. FROMV$statnamen,V$sesstatv 3. WHEREv.Sid=&sid 4. ANDv.Statistic#=n.Statistic# 5. ORDERBYn.CLASS,n.Statistic#7、查询耗资源
8、的进程（top session）1. SELECTs.SchemanameSchema_Name, 2. Decode(Sign(48-Command), 3. 1,To_Char(Command),ActionCode#|To_Char(Command)Action, 4. StatusSession_Status,s.OsuserOs_User_Name,s.Sid,p.Spid, 5. s.Serial#Serial_Num,Nvl(s.Username,Oracleprocess)User_Name, 6. s.TerminalTerminal,s.ProgramProgram,St.
9、VALUECriteria_Value 7. FROMV$sesstatSt,V$sessions,V$processp 8. WHERESt.Sid=s.Sid 9. ANDSt.Statistic#=To_Number(38) 10. AND(ALL=ALLORs.Status=ALL) 11. ANDp.Addr=s.Paddr 12. ORDERBYSt.VALUEDESC,p.SpidASC,s.UsernameASC,s.OsuserASC8、查看锁（lock）情况1. SELECT/*+RULE*/ 2. Ls.OsuserOs_User_Name,Ls.UsernameUser
10、_Name, 3. Decode(Ls.TYPE, 4. RW,Rowwaitenqueuelock,TM,DMLenqueuelock, 5. TX,Transactionenqueuelock,UL,Usersuppliedlock)Lock_Type, 6. o.Object_NameOBJECT, 7. Decode(Ls.Lmode, 8. 1,NULL,2,RowShare,3,RowExclusive, 9. 4,Share,5,ShareRowExclusive,6,Exclusive, 10. NULL)Lock_Mode, 11. o.Owner,Ls.Sid,Ls.Ser
11、ial#Serial_Num,Ls.Id1,Ls.Id2 12. FROMSys.Dba_Objectso, 13. (SELECTs.Osuser,s.Username,l.TYPE,l.Lmode,s.Sid,s.Serial#,l.Id1, 14. l.Id2 15. FROMV$sessions,V$lockl 16. WHEREs.Sid=l.Sid)Ls 17. WHEREo.Object_Id=Ls.Id1 18. ANDo.OwnerSYS 19. ORDERBYo.Owner,o.Object_Name9、查看等待（wait）情况1. SELECTWs.CLASS,Ws.CO
12、UNTCOUNT,SUM(Ss.VALUE)Sum_Value 2. FROMV$waitstatWs,V$sysstatSs 3. WHERESs.NAMEIN(dbblockgets,consistentgets) 4. GROUPBYWs.CLASS,Ws.COUNT10、求process/session的状态1. SELECTp.Pid,p.Spid,s.Program,s.Sid,s.Serial# 2. FROMV$processp,V$sessions 3. WHEREs.Paddr=p.Addr;11、求谁阻塞了某个session(10g)1. SELECTSid,Userna
13、me,Event,Blocking_Session,Seconds_In_Wait,Wait_Time 2. FROMV$session 3. WHEREStateIN(WAITING) 4. ANDWait_Class!=Idle;12、查会话的阻塞1. coluser_nameformata32 2. SELECT/*+rule*/ 3. Lpad(,Decode(l.Xidusn,0,3,0)|l.Oracle_UsernameUser_Name, 4. o.Owner,o.Object_Name,s.Sid,s.Serial# 5. FROMV$locked_Objectl,Dba_Objectso,V$sessions 6. WHEREl.Object_Id=o.Object_Id 7. ANDl.Session_Id=s.Sid 8. ORDERBYo.Object_Id,XidusnDESC; 9. colusernameformata15 10. collock_levelformata8 11. colownerformata18 12. colobject_nameformata32 13. SELECT/*+rule*/ 14. s.Usern
