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%'数据库安装选项colparameterformata40colvalueformata20select*fromv$option;检查SHARED_POOL判断SHARED_POOL中的对象SELECTtype,kept,COUNT(*),SUM(sharable_mem)FROMV$DB_OBJECT_CACHEGROUPBYtype,kept;将大对象缓存在SHARED_POOL中SELECTowner,name,sharable_mem,keptFROMV$DB_OBJECT_CACHEWHEREsharable_mem1024000ANDkept='NO'ORDERBYsharable_memDESC;SHARED_POOL中的剩余内存SELECT*FROMV$SGASTATWHERENAME='freememory'ANDPOOL='sharedpool';SGA区中各个对象的大小SELECTpool,name,round(bytes/1024/1024,2)bytesFROMv$sgastatORDERBYpool,name;检查PGA判断workarea的使用情况SELECTname,valueFROMv$sysstatWHEREnameLIKE'%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_readsFROMV$DB_CACHE_ADVICEWHEREname='DEFAULT'ANDblock_size=(SELECTvalueFROMV$PARAMETERWHEREname='db_block_size')ANDadvice_status='ON';查询DB_CACHE_SIZE中的对象COLobject_nameFORA60SELECTo.OBJECT_NAME,COUNT(*)NUMBER_OF_BLOCKSFROMDBA_OBJECTSo,V$BHbhWHEREo.DATA_OBJECT_ID=bh.OBJDANDo.OWNER!='SYS'GROUPBYo.OBJECT_NAMEORDERBYCOUNT(*);日志切换altersessionsetnls_date_format='yyyy-MM-ddHH24:MI:SS';selectfirst_timefromv$log_historywherethread#=1orderbyfirst_time;处理两阶段事务SELECTlocal_tran_idFROMdba_2pc_pending;Executedbms_transaction.purge_lost_db_entry('LOCAL_TRAN_ID');Commit;检查大事务查看运行时间长的事务：select*from(selectp.spid,s.sid,s.serial#,s.TADDR,t.ADDR,t.START_TIME,sysdatefromv$processp,v$transactiont,v$sessionswheres.usernameisnotnullandp.addr=s.paddrandt.addr=s.taddrorderbyt.START_TIME)whererownum10;检查SESSION引发的I/O变化SELECTs.sid,s.serial#,s.username,s.program,i.block_changesFROMv$sessions,v$sess_ioiWHEREs.sid=i.sidORDERBY5desc,1,2,3,4;检查消耗回滚段的事务SELECTs.sid,s.serial#,s.username,s.program,t.used_ublk,t.used_urecFROMv$sessions,v$transactiontWHEREs.taddr=t.addrORDERBY5desc,6desc,1,2,3,4;SELECTV.SID,V.SERIAL#,V.USERNAME,U.SEGMENT_NAME,U.BLOCKSFROMV$SESSIONV,V$TRANSACTIONT,DBA_ROLLBACK_SEGSR,DBA_UNDO_EXTENTSUWHEREV.SADDR=T.SES_ADDRANDR.SEGMENT_NAME=U.SEGMENT_NAMEANDU.STATUS='ACTIVE'ANDT.XIDUSN=R.SEGMENT_IDANDR.TABLESPACE_NAME='UNDOTBS1';查看回滚段正在处理的事务colnamefora12selecta.name,b.xacts,c.sid,c.serial#,d.sql_textfromv$rollnamea,v$rollstatb,v$sessionc,v$sqltextd,v$transactionewherea.usn=b.usnandb.usn=e.xidusnandc.taddr=e.addrandc.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_textFROMv$transactiont,v$sessions,v$rollstatr,dba_rollback_segsrs,v$sqltextsq,v$processprWHEREs.saddr=t.ses_addrANDt.xidusn=r.usnANDrs.segment_id=t.xidusnANDs.sql_address=sq.addressANDs.sql_hash_value=sq.hash_valueANDs.PADDR=pr.ADDRORDERBYt.used_ublkDESC,sq.PIECE;杀掉对应PENDINGOFFLINE状态回滚段上事务SELECTa.usn,a.name,b.status,c.tablespace_name,d.addr,e.sid,e.serial#,e.username,e.program,e.machine,e.osuserFROMv$rollnamea,v$rollstatb,dba_rollback_segsc,v$transactiond,v$sessioneWHEREa.usn=b.usnANDa.name=c.segment_nameANDa.usn=d.xidusnANDd.addr=e.taddrANDb.status='PENDINGOFFLINE';ALTERSYSTEMKILLSESSION'sid#,serial#';检查磁盘I/OCOLNAMEFORA60SETLINES200SELECTNAME,PHYRDS,PHYWRTSFROMV$DATAFILEdf,V$FILESTATfsWHEREdf.FILE#=fs.FILE#;检查latch检查Session获取那些LatchSELECTs.sql_hash_value,l.nameFROMV$SESSIONs,V$LATCHHOLDERlWHEREs.sid=l.sid;检查Latch的命中率SELECTnamespace,gets,100*gethits/getsgethitratio,pins,100*pinhits/pinsgetpinratio,reloads,invalidationsFROMV$LIBRARYCACHEORDERBYgetsDESC查看当前SESSION等待Latch类型SELECTn.name,SUM(w.p3)SleepsFROMV$SESSION_WAITw,V$LATCHNAMEnWHEREw.event='latchfree'ANDw.p2=n.latch#GROUPBYn.name;等待latch语句执行语句的Hash值SELECTs.sql_hash_value,l.nameFROMV$SESSIONs,V$LATCHHOLDERlWHEREs.sid=l.sid;热块的处理selectCHILD#,ADDRsADDR,GETSsGETS,MISSESsMISSES,SLEEPSsSLEEPSfromv$latch_childrenwherename='cachebufferschains'orderby4,1,2,3;--x.hladdr对应上面查询出来的sADDRselect/*+RULE*/e.owner||'.'||e.segment_namesegment_name,e.extent_idextent#,x.dbablk-e.block_id+1block#,x.tch,l.child#fromsys.v$latch_childrenl,sys.x$bhx,sys.dba_extentsewherex.hladdr='52FD1D18'(这个值对应上面查的addr的值)ande.file_id=x.file#andx.hladdr=l.addrandx.dbablkbetweene.block_idande.block_id+e.blocks-1orderbyx.tchdescSELECTowner,segment_nameFROMDBA_EXTENTSWHEREfile_id=&p1AND&p2betweenblock_idANDblock_id+blocks-1;检查lock,enqueue查看是否有锁定对象selecta.usernameusername,a.osuserosuser,a.sid,a.serial#,c.object_namefromv$sessiona,v$locked_objectb,dba_objectscwhereb.object_id=c.object_idandsid=b.session_id;查看锁以及对应的会话信息：set