资源描述：
《oracledba管理脚本》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、--监控索引是否使用alterindex&index_namemonitoringusage;alterindex&index_namenomonitoringusage;select*fromv$object_usagewhereindex_name=&index_name;--求数据文件的I/O分布selectdf.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetimfromv$filestatfs,v$dbfiledfwherefs.file#=df.file#ord
2、erbydf.name;--求某个隐藏参数的值colksppinmformata54colksppstvlformata54selectksppinm,ksppstvlfromx$ksppipi,x$ksppcvcvwherecv.indx=pi.indxandpi.ksppinmlike'_%'escape''andpi.ksppinmlike'%meer%';--求系统中较大的latchselectname,sum(gets),sum(misses),sum(sleeps),sum(wait_time)fromv$latch_childrengro
3、upbynamehavingsum(gets)>50orderby2;--求归档日志的切换频率(生产系统可能时间会很长)selectstart_recid,start_time,end_recid,end_time,minutesfrom(selecttest.*,rownumasrnfrom(selectb.recidstart_recid,to_char(b.first_time,'yyyy-mm-ddhh24:mi:ss')start_time,a.recidend_recid,to_char(a.first_time,'yyyy-mm-ddhh24
4、:mi:ss')end_time,round(((a.first_time-b.first_time)*24)*60,2)minutesfromv$log_historya,v$log_historybwherea.recid=b.recid+1andb.first_time>sysdate-1orderbya.first_timedesc)test)ywherey.rn<30--求回滚段正在处理的事务selecta.name,b.xacts,c.sid,c.serial#,d.sql_textfromv$rollnamea,v$rollstatb,v$s
5、essionc,v$sqltextd,v$transactionewherea.usn=b.usnandb.usn=e.xidusnandc.taddr=e.addrandc.sql_address=d.addressandc.sql_hashvalue=d.hash_valueorderbya.name,c.sid,d.piece;--求出无效的对象select'alterprocedure'
6、
7、object_name
8、
9、'compile;'fromdba_objectswherestatus='INVALID'andwner='&'andobject_
10、typein('PACKAGE','PACKAGEBODY');/selectowner,object_name,object_type,statusfromdba_objectswherestatus='INVALID';--求process/session的状态selectp.pid,p.spid,s.program,s.sid,s.serial#fromv$processp,v$sessionswheres.paddr=p.addr;--求当前session的状态selectsn.name,ms.valuefromv$mystatms,v$statn
11、amesnwherems.statistic#=sn.statistic#andms.value>0;--求表的索引信息selectui.table_name,ui.index_namefromuser_indexesui,user_ind_columnsuicwhereui.table_name=uic.table_nameandui.index_name=uic.index_nameandui.table_namelike'&table_name%'anduic.column_name='&column_name';--显示表的外键信息colsearc
12、h_conditionformata54selecttable_n
