维护常用SQL语句.docx
《维护常用SQL语句.docx》由会员分享，可在线阅读，更多相关《维护常用SQL语句.docx（10页珍藏版）》请在冰豆网上搜索。
维护常用SQL语句
Oracle数据库维护常用SQL语句集合
-
进程相关：
1、求当前会话的SID，SERIAL#
SELECTSid,Serial#
FROMV$session
WHEREAudsid=Sys_Context（'USERENV','SESSIONID'）;
2、查询session的OS进程ID
SELECTp.Spid"OSThread",b.NAME"Name-User",s.Program,s.Sid,s.Serial#,
s.Osuser,s.Machine
FROMV$processp,V$sessions,V$bgprocessb
WHEREp.Addr=s.Paddr
ANDp.Addr=b.Paddr
And（s.sid=&1orp.spid=&1）
UNIONALL
SELECTp.Spid"OSThread",s.Username"Name-User",s.Program,s.Sid,
s.Serial#,s.Osuser,s.Machine
FROMV$processp,V$sessions
WHEREp.Addr=s.Paddr
And（s.sid=&1orp.spid=&1）
ANDs.UsernameISNOTNULL;
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,SYSDATEStart_Time,
SYSDATEFinish_Time,'>'||AddressSql_Address,'N'Status
FROMV$sqlarea
WHEREAddress=（SELECTSql_Address
FROMV$session
WHERESid=&sid）;
4、查找object为哪些进程所用
SELECTp.Spid,s.Sid,s.Serial#Serial_Num,s.UsernameUser_Name,
a.TYPEObject_Type,s.OsuserOs_User_Name,a.Owner,
a.OBJECTObject_Name,
Decode（Sign（48-Command）,1,To_Char（Command）,'ActionCode#'||To_Char（Command））Action,
p.ProgramOracle_Process,s.TerminalTerminal,s.ProgramProgram,
s.StatusSession_Status
FROMV$sessions,V$accessa,V$processp
WHEREs.Paddr=p.Addr
ANDs.TYPE='USER'
ANDa.Sid=s.Sid
ANDa.OBJECT='&obj'
ORDERBYs.Username,s.Osuser
5、查看有哪些用户连接
SELECTs.OsuserOs_User_Name,
Decode（Sign（48-Command）,1,To_Char（Command）,
'ActionCode#'||To_Char（Command））Action,
p.ProgramOracle_Process,StatusSession_Status,s.TerminalTerminal,
s.ProgramProgram,s.UsernameUser_Name,
s.Fixed_Table_SequenceActivity_Meter,''Query,0Memory,
0Max_Memory,0Cpu_Usage,s.Sid,s.Serial#Serial_Num
FROMV$sessions,V$processp
WHEREs.Paddr=p.Addr
ANDs.TYPE='USER'
ORDERBYs.Username,s.Osuser
6、根据v.sid查看对应连接的资源占用等情况
SELECTn.NAME,v.VALUE,n.CLASS,n.Statistic#
FROMV$statnamen,V$sesstatv
WHEREv.Sid=&sid
ANDv.Statistic#=n.Statistic#
ORDERBYn.CLASS,n.Statistic#
7、查询耗资源的进程（topsession）
SELECTs.SchemanameSchema_Name,
Decode（Sign（48-Command）,
1,To_Char（Command）,'ActionCode#'||To_Char（Command））Action,
StatusSession_Status,s.OsuserOs_User_Name,s.Sid,p.Spid,
s.Serial#Serial_Num,Nvl（s.Username,'[Oracleprocess]'）User_Name,
s.TerminalTerminal,s.ProgramProgram,St.VALUECriteria_Value
FROMV$sesstatSt,V$sessions,V$processp
WHERESt.Sid=s.Sid
ANDSt.Statistic#=To_Number（'38'）
AND（'ALL'='ALL'ORs.Status='ALL'）
ANDp.Addr=s.Paddr
ORDERBYSt.VALUEDESC,p.SpidASC,s.UsernameASC,s.OsuserASC
8、查看锁（lock）情况
SELECT/*+RULE*/
Ls.OsuserOs_User_Name,Ls.UsernameUser_Name,
Decode（Ls.TYPE,
'RW','Rowwaitenqueuelock','TM','DMLenqueuelock',
'TX','Transactionenqueuelock','UL','Usersuppliedlock'）Lock_Type,
o.Object_NameOBJECT,
Decode（Ls.Lmode,
1,NULL,2,'RowShare',3,'RowExclusive',
4,'Share',5,'ShareRowExclusive',6,'Exclusive',
NULL）Lock_Mode,
o.Owner,Ls.Sid,Ls.Serial#Serial_Num,Ls.Id1,Ls.Id2
FROMSys.Dba_Objectso,
（SELECTs.Osuser,s.Username,l.TYPE,l.Lmode,s.Sid,s.Serial#,l.Id1,
l.Id2
FROMV$sessions,V$lockl
WHEREs.Sid=l.Sid）Ls
WHEREo.Object_Id=Ls.Id1
ANDo.Owner<>'SYS'
ORDERBYo.Owner,o.Object_Name
9、查看等待（wait）情况
SELECTWs.CLASS,Ws.COUNTCOUNT,SUM（Ss.VALUE）Sum_Value
FROMV$waitstatWs,V$sysstatSs
WHERESs.NAMEIN（'dbblockgets','consistentgets'）
GROUPBYWs.CLASS,Ws.COUNT
10、求process/session的状态
SELECTp.Pid,p.Spid,s.Program,s.Sid,s.Serial#
FROMV$processp,V$sessions
WHEREs.Paddr=p.Addr;
11、求谁阻塞了某个session（10g）
SELECTSid,Username,Event,Blocking_Session,Seconds_In_Wait,Wait_Time
FROMV$session
WHEREStateIN（'WAITING'）
ANDWait_Class!
='Idle';
12、查会话的阻塞
coluser_nameformata32
SELECT/*+rule*/
Lpad（'',Decode（l.Xidusn,0,3,0））||l.Oracle_UsernameUser_Name,
o.Owner,o.Object_Name,s.Sid,s.Serial#
FROMV$locked_Objectl,Dba_Objectso,V$sessions
WHEREl.Object_Id=o.Object_Id
ANDl.Session_Id=s.Sid
ORDERBYo.Object_Id,XidusnDESC;
colusernameformata15
collock_levelformata8
colownerformata18
colobject_nameformata32
SELECT/*+rule*/
s.Username,
Decode（l.TYPE,'tm','tablelock','tx','rowlock',NULL）Lock_Level,
o.Owner,o.Object_Name,s.Sid,s.Serial#
FROMV$sessions,V$lockl,Dba_Objectso
WHEREl.Sid=s.Sid
ANDl.Id1=o.Object_Id（+）
ANDs.UsernameISNOTNULL;
13、求等待的事件及会话信息/求会话的等待及会话信息
SELECTSe.Sid,s.Username,Se.Event,Se.Total_Waits,Se.Time_Waited,
Se.Average_Wait
FROMV$sessions,V$session_EventSe
WHEREs.UsernameISNOTNULL
ANDSe.Sid=s.Sid
ANDs.Status='ACTIVE'
ANDSe.EventNOTLIKE'%SQL*Net%'
ORDERBYs.Username;
SELECTs.Sid,s.Username,Sw.Event,Sw.Wait_Time,Sw.State,
Sw.Seconds_In_Wait
FROMV$sessions,V$session_WaitSw
WHEREs.UsernameISNOTNULL
ANDSw.Sid=s.Sid
ANDSw.EventNOTLIKE'%SQL*Net%'
ORDERBYs.Username;
14、求会话等待的file_id/block_id
coleventformata24
colp1textformata12
colp2textformata12
colp3textformata12
SELECTSid,Event,P1text,P1,P2text,P2,P3text,P3
FROMV$session_Wait
WHEREEventNOTLIKE'%SQL%'
ANDEventNOTLIKE'%rdbms%'
ANDEventNOTLIKE'%mon%'
ORDERBYEvent;
SELECTNAME,Wait_Time
FROMV$latchl
WHEREEXISTS（SELECT1
FROM（SELECTSid,Event,P1text,P1,P2text,P2,P3text,P3
FROMV$session_Wait
WHEREEventNOTLIKE'%SQL%'
ANDEventNOTLIKE'%rdbms%'
ANDEventNOTLIKE'%mon%'）x
WHEREx.P1=l.Latch#）;
15、求会话等待的对象
colownerformata18
colsegment_nameformata32
colsegment_typeformata32
SELECTOwner,Segment_Name,Segment_Type
FROMDba_Extents
WHEREFile_Id=&File_Id
AND&Block_IdBETWEENBlock_IdANDBlock_Id+Blocks-1;
16、求出某个进程，并对它进行跟踪
SELECTs.Sid,s.Serial#
FROMV$sessions,V$processp
WHEREs.Paddr=p.Addr
ANDp.Spid=&1;
ExecDbms_System.Set_Sql_Trace_In_Session（&1,&2,TRUE）;
ExecDbms_System.Set_Sql_Trace_In_Session（&1,&2,FALSE）;
17、求当前session的跟踪文件
SELECTP1.VALUE||'/'||P2.VALUE||'_ora_'||p.Spid||'.ora'Filename
FROMV$processp,V$sessions,V$parameterP1,V$parameterP2
WHEREP1.NAME='user_dump_dest'
ANDP2.NAME='instance_name'
ANDp.Addr=s.Paddr
ANDs.Audsid=Userenv（'SESSIONID'）
ANDp.BackgroundISNULL
ANDInstr（p.Program,'CJQ'）=0;
18、求出锁定的对象
SELECTDo.Object_Name,Session_Id,Process,Locked_Mode
FROMV$locked_ObjectLo,Dba_ObjectsDo
WHERELo.Object_Id=Do.Object_Id;
-
资料引用:
升级会员 