维护常用SQL语句.docx
- 文档编号:8982559
- 上传时间:2023-02-02
- 格式:DOCX
- 页数:10
- 大小:16.71KB
维护常用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;
-
资料引用:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 维护 常用 SQL 语句
![提示](https://static.bdocx.com/images/bang_tan.gif)