資源描述：
《oracledba管理腳本》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫(kù)。
1、--監(jiān)控索引是否使用alterindex&index_namemonitoringusage;alterindex&index_namenomonitoringusage;select*fromv$object_usagewhereindex_name=&index_name;--求數(shù)據(jù)文件的I/O分布selectdf.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetimfromv$filestatfs,v$dbfiledfwherefs.file#=df.file#ord
2、erbydf.name;--求某個(gè)隱藏參數(shù)的值colksppinmformata54colksppstvlformata54selectksppinm,ksppstvlfromx$ksppipi,x$ksppcvcvwherecv.indx=pi.indxandpi.ksppinmlike'_%'escape''andpi.ksppinmlike'%meer%';--求系統(tǒng)中較大的latchselectname,sum(gets),sum(misses),sum(sleeps),sum(wait_time)fromv$latch_childrengro
3、upbynamehavingsum(gets)>50orderby2;--求歸檔日志的切換頻率(生產(chǎn)系統(tǒng)可能時(shí)間會(huì)很長(zhǎng))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--求回滾段正在處理的事務(wù)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;--求出無(wú)效的對(duì)象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的狀態(tài)selectp.pid,p.spid,s.program,s.sid,s.serial#fromv$processp,v$sessionswheres.paddr=p.addr;--求當(dāng)前session的狀態(tài)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
