oracle常用脚本自己总结的.docx
《oracle常用脚本自己总结的.docx》由会员分享，可在线阅读，更多相关《oracle常用脚本自己总结的.docx（30页珍藏版）》请在冰豆网上搜索。
oracle常用脚本自己总结的
日常工作中常用的脚本总结
2009-07
在做数据库维护过程中，需要使用大量的脚本反馈数据库的相关信息。
在日常工作中，可以使用下列脚本查询数据库中相关的等待事件。
查找数据库中具体的事件信息。
具体脚本内容如下：
查询数据库信息
selecta.name,e.global_name,b.banner,c.host_name,c.instance_name,c.startup_time,RESTRICTED,a.log_mode,a.open_mode,fromv$databasea,v$versionb,v$instancec,global_nameeWHEREb.bannerLIKE'%Oracle%'
数据库安装选项
colparameterformata40
colvalueformata20
select*fromv$option;
检查SHARED_POOL
判断SHARED_POOL 中的对象
SELECTtype,kept,COUNT（*）,SUM（sharable_mem）
FROMV$DB_OBJECT_CACHE
GROUPBYtype,kept;
将大对象缓存在SHARED_POOL 中
SELECTowner,name,sharable_mem,kept
FROMV$DB_OBJECT_CACHE
WHEREsharable_mem>1024000
ANDkept='NO'
ORDERBYsharable_memDESC;
SHARED_POOL中的剩余内存
SELECT*FROMV$SGASTAT
WHERENAME='freememory'
ANDPOOL='sharedpool';
SGA区中各个对象的大小
SELECTpool,name,round（bytes/1024/1024,2）bytes
FROMv$sgastat
ORDERBYpool,name;
检查PGA
判断workarea的使用情况
SELECTname,value
FROMv$sysstat
WHEREname
LIKE'%workarea%';
判断用户消耗pga空间比例
SELECTsum（PGA_USED_MEM）,sum（PGA_ALLOC_MEM）,sum（PGA_MAX_MEM）
FROMv$process;
检查DB_CACHE_SIZE
估算DB_CACHE_SIZE大小
COLUMNsize_for_estimateFORMAT999,999,999,999heading'CacheSize（MB）'
COLUMNbuffers_for_estimateFORMAT999,999,999heading'Buffers'
COLUMNestd_physical_read_factorFORMAT999.90heading'EstdPhys|ReadFactor'
COLUMNestd_physical_readsFORMAT999,999,999heading'EstdPhys|Reads'
SELECTsize_for_estimate,buffers_for_estimate,estd_physical_read_factor,estd_physical_reads
FROMV$DB_CACHE_ADVICE
WHEREname='DEFAULT'
ANDblock_size=（SELECTvalueFROMV$PARAMETERWHEREname='db_block_size'）
ANDadvice_status='ON';
查询DB_CACHE_SIZE中的对象
COLobject_nameFORA60
SELECTo.OBJECT_NAME,COUNT（*）NUMBER_OF_BLOCKS
FROMDBA_OBJECTSo,V$BHbh
WHEREo.DATA_OBJECT_ID=bh.OBJD
ANDo.OWNER!
='SYS'
GROUPBYo.OBJECT_NAME
ORDERBYCOUNT（*）;
日志切换
altersessionsetnls_date_format='yyyy-MM-ddHH24:
MI:
SS';
selectfirst_timefromv$log_historywherethread#=1orderbyfirst_time;
处理两阶段事务
SELECTlocal_tran_id
FROMdba_2pc_pending;
Executedbms_transaction.purge_lost_db_entry（''）;
Commit;
检查大事务
查看运行时间长的事务：
select*from（
selectp.spid,s.sid,s.serial#,s.TADDR,t.ADDR,t.START_TIME,sysdate
fromv$processp,v$transactiont,v$sessions
where
s.usernameisnotnull
andp.addr=s.paddr
andt.addr=s.taddr
orderbyt.START_TIME）
whererownum<10;
检查SESSION引发的I/O变化
SELECTs.sid,s.serial#,s.username,s.program,
i.block_changes
FROMv$sessions,v$sess_ioi
WHEREs.sid=i.sid
ORDERBY5desc,1,2,3,4;
检查消耗回滚段的事务
SELECTs.sid,s.serial#,s.username,s.program,
t.used_ublk,t.used_urec
FROMv$sessions,v$transactiont
WHEREs.taddr=t.addr
ORDERBY5desc,6desc,1,2,3,4;
SELECTV.SID,V.SERIAL#,V.USERNAME,U.SEGMENT_NAME,U.BLOCKS
FROMV$SESSIONV,V$TRANSACTIONT,DBA_ROLLBACK_SEGSR,DBA_UNDO_EXTENTSU
WHEREV.SADDR=T.SES_ADDRAND
R.SEGMENT_NAME=U.SEGMENT_NAMEAND
U.STATUS='ACTIVE'AND
T.XIDUSN=R.SEGMENT_IDAND
R.TABLESPACE_NAME='UNDOTBS1';
查看回滚段正在处理的事务
colnamefora12
selecta.name,b.xacts,c.sid,c.serial#,d.sql_text
fromv$rollnamea,v$rollstatb,v$sessionc,v$sqltextd,v$transactione
wherea.usn=b.usnandb.usn=e.xidusnandc.taddr=e.addr
andc.sql_address=d.addressandc.sql_hash_value=d.hash_valueorderbya.name,c.sid,d.piece;
SELECTs.username,
s.sid,
pr.PID,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
rs.segment_id,
r.usn,
rs.segment_name,
r.rssize/1024/1024,
sq.sql_text
FROMv$transactiont,v$sessions,v$rollstatr,dba_rollback_segsrs,v$sqltextsq,v$processpr
WHEREs.saddr=t.ses_addr
ANDt.xidusn=r.usn
ANDrs.segment_id=t.xidusn
ANDs.sql_address=sq.address
ANDs.sql_hash_value=sq.hash_value
ANDs.PADDR=pr.ADDR
ORDERBYt.used_ublkDESC,sq.PIECE;
杀掉对应PENDINGOFFLINE状态回滚段上事务
SELECT
a.usn,
a.name,
b.status,
c.tablespace_name,
d.addr,
e.sid,
e.serial#,
e.username,
e.program,
e.machine,
e.osuser
FROM
v$rollnamea,
v$rollstatb,
dba_rollback_segsc,
v$transactiond,
v$sessione
WHERE
a.usn=b.usnAND
a.name=c.segment_nameAND
a.usn=d.xidusnAND
d.addr=e.taddrAND
b.status='PENDINGOFFLINE';
ALTERSYSTEMKILLSESSION',';
检查磁盘I/O
COLNAMEFORA60
SETLINES200
SELECTNAME,PHYRDS,PHYWRTS
FROMV$DATAFILEdf,V$FILESTATfs
WHEREdf.FILE#=fs.FILE#;
检查latch
检查Session获取那些Latch
SELECTs.sql_hash_value,l.name
FROMV$SESSIONs,V$LATCHHOLDERl
WHEREs.sid=l.sid;
检查Latch的命中率
SELECTnamespace,gets,100*gethits/getsgethitratio,
pins,100*pinhits/pinsgetpinratio,
reloads,invalidations
FROMV$LIBRARYCACHE
ORDERBYgetsDESC
查看当前SESSION等待Latch类型
SELECTn.name,SUM（w.p3）Sleeps
FROMV$SESSION_WAITw,V$LATCHNAMEn
WHEREw.event='latchfree'
ANDw.p2=n.latch#
GROUPBYn.name;
等待latch语句执行语句的Hash值
SELECTs.sql_hash_value,l.name
FROMV$SESSIONs,V$LATCHHOLDERl
WHEREs.sid=l.sid;
热块的处理
select
CHILD#,ADDR"sADDR",GETS"sGETS",MISSES"sMISSES",SLEEPS"sSLEEPS"
fromv$latch_children
wherename='cachebufferschains'
orderby4,1,2,3;
--x.hladdr对应上面查询出来的"sADDR"
select/*+RULE*/
e.owner||'.'||e.segment_namesegment_name,
e.extent_idextent#,
x.dbablk-e.block_id+1block#,
x.tch,
l.child#
from
sys.v$latch_childrenl,
sys.x$bhx,
sys.dba_extentse
where
x.hladdr='52FD1D18'（这个值对应上面查的addr的值）
and
e.file_id=x.file#
and
x.hladdr=l.addr
and
x.dbablkbetweene.block_id
ande.block_id+e.blocks-1
orderbyx.tchdesc
SELECTowner,segment_name
FROMDBA_EXTENTS
WHEREfile_id=&p1
AND&p2betweenblock_idANDblock_id+blocks-1;
检查lock,enqueue
查看是否有锁定对象
selecta.usernameusername,
a.osuserosuser,
a.sid,
a.serial#,
c.object_name
fromv$sessiona,
v$locked_objectb,
dba_objectscwhereb.object_id=c.object_id
and
sid=b.session_id;
查看锁以及对应的会话信息：
setlinesize132pagesize9999
--breakonKillonusernameonterminal
columnKillheading'KillString'formata13
columnresheading'ResourceType'format999
columnid1format9999990
columnid2format9999990
columnlmodeheading'LockHeld'formata20
columnrequestheading'LockRequested'formata20
columnserial#format99999
columnusernameformata10heading"Username"
columnterminalheadingTermformata15
columntabformata35heading"TableName"
columnownerformata9
columnAddressformata18
selectnvl（S.USERNAME,'Internal'）username,
nvl（S.TERMINAL,'None'）terminal,
L.SID||','||S.SERIAL#Kill,
U1.NAME||'.'||substr（T1.NAME,1,20）tab,
decode（L.LMODE,1,'NoLock',
2,'RowShare',
3,'RowExclusive',
4,'Share',
5,'ShareRowExclusive',
6,'Exclusive',null）lmode,
decode（L.REQUEST,1,'NoLock',
2,'RowShare',
3,'RowExclusive',
4,'Share',
5,'ShareRowExclusive',
6,'Exclusive',null）request
fromV$LOCKL,
V$SESSIONS,
SYS.USER$U1,
SYS.OBJ$T1
whereL.SID=S.SID
andT1.OBJ#=decode（L.ID2,0,L.ID1,L.ID2）
andU1.USER#=T1.OWNER#
andS.TYPE!
='BACKGROUND'
orderby1,2,5;
检查对应SESSION持有锁
SELECTlpad（'',DECODE（request,0,0,1））||sidsess,id1,id2,lmode,request,type
FROMV$LOCK
WHEREid1IN（SELECTid1FROMV$LOCKWHERElmode=0）
ORDERBYid1,request;
SESSION阻塞
SELECTdecode（request,0,'holder:
','waiter:
'）||sidsid,id1,id2,lmode,request,type
FROMv$lock
WHERE（id1,id2,type）IN
（SELECTid1,id2,type
FROMv$lock
WHERErequest>0）
ORDERBYid1,request;
RAC环境下的锁
setlinesize100
setpagesize66
colc1fora15
colc1heading"ProgramName"
SELECTl.inst_id,l.SID,programc1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
FROMgv$lockl,gv$sessions
WHEREl.typeLIKE'TX'
ANDl.request=6
ANDl.inst_id=s.inst_id
ANDl.sid=s.sid
ORDERBYid1;
当前系统等待的enqueue类型
selectsid,event,p1,p1raw,chr（bitand（P1,-16777216）/16777215）||chr（bitand（P1,16711680）/65535）type,
mod（P1,16）"MODE"
fromv$session_wait
whereevent='enqueue';
SELECTeq_type"Lock",
total_req#"Gets",
total_wait#"Waits",
cum_wait_time
FROMv$enqueue_stat
WHEREtotal_wait#>0
ORDERBYcum_wait_timeDESC;
SELECT/*+ordered*/
a.sidblocker_sid,
a.serial#,
a.usernameblocker_username,
f.spid,
a.program,
a.logon_time,
d.event,
b.TYPE,
b.lmodemode_held,
b.block,
b.ctimetime_held,
c.sidwaiter_sid,
c.requestrequest_mode,
c.ctimetime_waited,
g.sql_text
FROMv$lockb,
v$enqueue_lockc,
v$sessiona,
v$session_waitd,
v$processf,
v$sqlareag
WHEREa.sid=b.sid
ANDb.id1=c.id1（+）
ANDb.id2=c.id2（+）
ANDc.TYPE（+）='TX'
ANDb.TYPE='TX'
ANDb.BLOCK=1
ANDd.sid=a.sid
ANDa.paddr=f.addr
ANDg.hash_value=a.sql_hash_value
ORDERBYtime_held,time_waited;
SELECTusername,
v$lock.sid,
trunc（id1/power（2,16））rbs,
bitand（id1,to_number（'ffff','xxxx'））+0slot,--mod（id1,65536）slot,
id2seq,
v$lock.typetype,
lmode,
request,
block
FROMv$lock,v$session
WHEREv$lock.TYPE='TX'
ANDv$lock.sid=v$session.sid
ANDv$session.username=USER;
查看enqueue等待的语句及类型
colsql_textfora40
selectse.username,sq.sql_text,chr（bitand（sw.P1,-16777216）/16777215）||chr（bitand（sw.P1,16711680）/65535）type,sw.p2id1
fromv$sessionse,v$session_waitsw,v$sqlareasq
wherese.sid=sw.sid
andse.sql_hash_value=sq.hash_value
andsw.event='enqueue';
检查由于未加索引的外键引起的全表锁的表：
columncolumnsformata30word_wrapped
columntablenameformata15word_wrapped
columnconstraint_nameformata15word_wrapped
selecttable_name,constraint_name,
cname1||nvl2（cname2,','||cname2,null）||
nvl2（cname3,','||cname3,null）||nvl2（cname4,','||cname4,null）||
nvl2（cname5,','||cname5,null）||nvl2（cname6,','||cname6,null）||
nvl2（cname7,','||cname7,null）||nvl2（cname8,','||cname8,null）
columns
from（selectb.table_name,
b.constraint_name,
升级会员 