维护常用SQL语句.docx
《维护常用SQL语句.docx》由会员分享,可在线阅读,更多相关《维护常用SQL语句.docx(10页珍藏版)》请在冰豆网上搜索。
![维护常用SQL语句.docx](https://file1.bdocx.com/fileroot1/2023-2/2/4b77190b-7ab6-4809-a25e-d8bd2b3ac91f/4b77190b-7ab6-4809-a25e-d8bd2b3ac91f1.gif)
维护常用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;
-
资料引用: