Oracle数据库DBA日常Sql列表

--监控索引是否使用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#orderbydf.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_childrengroupbynamehavingsum(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: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_timesysdate-1orderbya.first_timedesc)test)ywherey.rn30--求回滚段正在处理的事务selecta.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_hashvalue=d.hash_valueorderbya.name,c.sid,d.piece;--求出无效的对象select'alterprocedure'||object_name||'compile;'fromdba_objectswherestatus='INVALID'andwner='&'andobject_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$statnamesnwherems.statistic#=sn.statistic#andms.value0;--求表的索引信息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';--显示表的外键信息colsearch_conditionformata54selecttable_name,constraint_namefromuser_constraintswhereconstraint_type='R'andconstraint_namein(selectconstraint_namefromuser_cons_columnswherecolumn_name='&1');selectrpad(child.table_name,25,'')child_tablename,rpad(cp.column_name,17,'')referring_column,rpad(parent.table_name,25,'')parent_tablename,rpad(pc.column_name,15,'')referred_column,rpad(child.constraint_name,25,'')constraint_namefromuser_constraintschild,user_constraintsparent,user_cons_columnscp,user_cons_columnspcwherechild.constraint_type='R'andchild.r_constraint_name=parent.constraint_nameandchild.constraint_name=cp.constraint_nameandparent.constraint_name=pc.constraint_nameandcp.position=pc.positionandchild.table_name='&table_name'orderbychild.owner,child.table_name,child.constraint_name,cp.position;--显示表的分区及子分区(user_tab_subpartitions)coltable_nameformata16colpartition_nameformata16colhigh_valueformata81selecttable_name,partition_name,HIGH_VALUEfromuser_tab_partitionswheretable_name='&table_name'--使用dbms_xplan生成一个执行计划explainplansetstatement_id='&sql_id'for&sql;select*fromtable(dbms_xplan.display);--求某个事务的重做信息(bytes)selects.name,m.valuefromv$mystatm,v$statnameswherem.statistic#=s.statistic#ands.namelike'%redosize%';--求cache中缓存超过其5%的对象selecto.owner,o.object_type,o.object_name,count(b.objd)fromv$bhb,dba_objectsowhereb.objd=o.object_idgroupbyo.owner,o.object_type,o.object_namehavingcount(b.objd)(selectto_number(value)*0.05fromv$parameterwherename='db_block_buffers');--求谁阻塞了某个session(10g)selectsid,username,event,blocking_session,seconds_in_wait,wait_timefromv$sessionwherestatein('WAITING')andwait_class!='Idle';--求session的OS进程IDcolprogramformata54selectp.spidOSThread,b.nameName-User,s.programfromv$processp,v$sessions,v$bgprocessbwherep.addr=s.paddrandp.addr=b.paddrUNIONALLselectp.spidOSThread,s.usernameName-User,s.programfromv$processp,v$sessionswherep.addr=s.paddrands.usernameisnotnull;--查会话的阻塞coluser_nameformata32select/*+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$sessionswherel.object_id=o.object_idandl.session_id=s.sidorderbyo.object_id,xidusndesc;colusernameformata15collock_levelformata8colownerformata18colobject_nameformata32select/*+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_objectsowherel.sid=s.sidandl.id1=o.object_id(+)ands.usernameisnotnull;--求等待的事件及会话信息/求会话的等待及会话信息selectse.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_waitfromv$sessions,v$session_eventsewheres.usernameisnotnullandse.sid=s.sidands.status='ACTIVE'andse.eventnotlike'%SQL*Net%'orderbys.username;selects.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_waitfromv$sessions,v$session_waitswwheres.usernameisnotnullandsw.sid=s.sidandsw.eventnotlike'%SQL*Net%'orderbys.username;--求会话等待的file_id/block_idcoleventformata24colp1textformata12colp2textformata12colp3textformata12selectsid,event,p1text,p1,p2text,p2,p3text,