管理员日常工作中必备的sql列表.docx

管理员日常工作中必备的sql列表.docx

《管理员日常工作中必备的sql列表.docx》由会员分享，可在线阅读，更多相关《管理员日常工作中必备的sql列表.docx（52页珍藏版）》请在冰豆网上搜索。

管理员日常工作中必备的sql列表

数据库管理员日常工作中必备的sql列表

　　--监控索引是否使用

　　alterindex&index_namemonitoringusage;

　　alterindex&index_namenomonitoringusage;

　　select*fromv$object_usagewhereindex_name=&index_name;

　　--求数据文件的I/O分布

　　selectdf.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetim

　　fromv$filestatfs,v$dbfiledf

　　wherefs.file#=df.file#orderbydf.name;

　　--求某个隐藏参数的值

　　colksppinmformata54

　　colksppstvlformata54

　　selectksppinm,ksppstvl

　　fromx$ksppipi,x$ksppcvcv

　　wherecv.indx=pi.indxandpi.ksppinmlike'\_%'escape'\'andpi.ksppinmlike'%meer%';

　　--求系统中较大的latch

　　selectname,sum（gets）,sum（misses）,sum（sleeps）,sum（wait_time）

　　fromv$latch_children

　　groupbynamehavingsum（gets）>50orderby2;

　　--求归档日志的切换频率（生产系统可能时间会很长）

　　selectstart_recid,start_time,end_recid,end_time,minutesfrom（selecttest.*,rownumasrn

　　from（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）minutes

　　fromv$log_historya,v$log_historybwherea.recid=b.recid+1andb.first_time>sysdate-1

　　orderbya.first_timedesc）test）ywherey.rn<30

　　--求回滚段正在处理的事务

　　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;

　　--求出无效的对象

　　select'alterprocedure'||object_name||'compile;'

　　fromdba_objects

　　wherestatus='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.value

　　fromv$mystatms,v$statnamesn

　　wherems.statistic#=sn.statistic#andms.value>0;

　　--求表的索引信息

　　selectui.table_name,ui.index_name

　　fromuser_indexesui,user_ind_columnsuic

　　whereui.table_name=uic.table_nameandui.index_name=uic.index_name

　　andui.table_namelike'&table_name%'anduic.column_name='&column_name';

　　--显示表的外键信息

　　colsearch_conditionformata54

　　selecttable_name,constraint_name

　　fromuser_constraints

　　whereconstraint_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_name

　　fromuser_constraintschild,user_constraintsparent,

　　user_cons_columnscp,user_cons_columnspc

　　wherechild.constraint_type='R'andchild.r_constraint_name=parent.constraint_nameand

　　child.constraint_name=cp.constraint_nameandparent.constraint_name=pc.constraint_nameand

　　cp.position=pc.positionandchild.table_name='&table_name'

　　orderbychild.owner,child.table_name,child.constraint_name,cp.position;

　　--显示表的分区及子分区（user_tab_subpartitions）

　　coltable_nameformata16

　　colpartition_nameformata16

　　colhigh_valueformata81

　　selecttable_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.value

　　fromv$mystatm,v$statnames

　　wherem.statistic#=s.statistic#ands.namelike'%redosize%';

　　--求cache中缓存超过其5%的对象

　　selecto.owner,o.object_type,o.object_name,count（b.objd）

　　fromv$bhb,dba_objectso

　　whereb.objd=o.object_id

　　groupbyo.owner,o.object_type,o.object_name

　　havingcount（b.objd）>（selectto_number（value）*0.05fromv$parameterwherename='db_block_buffers'）;

　　--求谁阻塞了某个session（10g）

　　selectsid,username,event,blocking_session,

　　seconds_in_wait,wait_time

　　fromv$sessionwherestatein（'WAITING'）andwait_class!

='Idle';

　　--求session的OS进程ID

　　colprogramformata54

　　selectp.spid"OSThread",b.name"Name-User",s.program

　　fromv$processp,v$sessions,v$bgprocessb

　　wherep.addr=s.paddrandp.addr=b.paddr

　　UNIONALL

　　selectp.spid"OSThread",s.username"Name-User",s.program

　　fromv$processp,v$sessionswherep.addr=s.paddrands.usernameisnotnull;

　　--查会话的阻塞

　　coluser_nameformata32

　　select/*+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$sessions

　　wherel.object_id=o.object_idandl.session_id=s.sidorderbyo.object_id,xidusndesc;

　　colusernameformata15

　　collock_levelformata8

　　colownerformata18

　　colobject_nameformata32

　　select/*+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_objectso

　　wherel.sid=s.sidandl.id1=o.object_id（+）ands.usernameisnotnull;

　　--求等待的事件及会话信息/求会话的等待及会话信息

　　selectse.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait

　　fromv$sessions,v$session_eventse

　　wheres.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_wait

　　fromv$sessions,v$session_waitsw

　　wheres.usernameisnotnullandsw.sid=s.sidandsw.eventnotlike'%SQL*Net%'orderbys.username;

　　--求会话等待的file_id/block_id

　　coleventformata24

　　colp1textformata12

　　colp2textformata12

　　colp3textformata12

　　selectsid,event,p1text,p1,p2text,p2,p3text,p3

　　fromv$session_wait

　　whereeventnotlike'%SQL%'andeventnotlike'%rdbms%'andeventnotlike'%mon%'orderbyevent;

　　selectname,wait_timefromv$latchlwhereexists（select1from（selectsid,event,p1text,p1,p2text,p2,p3text,p3

　　fromv$session_wait

　　whereeventnotlike'%SQL%'andeventnotlike'%rdbms%'andeventnotlike'%mon%'

　　）xwherex.p1=l.latch#）;

　　--求会话等待的对象

　　colownerformata18

　　colsegment_nameformata32

　　colsegment_typeformata32

　　selectowner,segment_name,segment_type

　　fromdba_extents

　　wherefile_id=&file_idand&block_idbetweenblock_idandblock_id+blocks-1;

　　--求buffercache中的块信息

　　selecto.OBJECT_TYPE,substr（o.OBJECT_NAME,1,10）objname,b.objd,b.status,count（b.objd）

　　fromv$bhb,dba_objectso

　　whereb.objd=o.data_object_idando.owner='&1'groupbyo.object_type,o.object_name,b.objd,b.status;

　　--求日志文件的空间使用

　　selectle.leseqcurrent_log_sequence#,100*cp.cpodr_bno/le.lesizpercentage_full

　　fromx$kcccpcp,x$kcclele

　　wherele.leseq=cp.cpodr_seq;

　　--求等待中的对象

　　select/*+rule*/s.sid,s.username,w.event,o.owner,o.segment_name,o.segment_type,

　　o.partition_name,w.seconds_in_waitseconds,w.state

　　fromv$session_waitw,v$sessions,dba_extentso

　　wherew.eventin（selectnamefromv$event_namewhereparameter1='file#'

　　andparameter2='block#'andnamenotlike'control%'）

　　ando.owner<>'sys'andw.sid=s.sidandw.p1=o.file_idandw.p2>=o.block_idandw.p2

　　--求当前事务的重做尺寸

　　selectvalue

　　fromv$mystat,v$statname

　　wherev$mystat.statistic#=v$statname.statistic#andv$statname.name='redosize';

　　--唤醒smon去清除临时段

　　columnpidnew_valueSmon

　　settermoutoff

　　selectp.pidfromsys.v_$bgprocessb,sys.v_$processpwhereb.name='SMON'andp.addr=b.paddr

　　/

　　settermouton

　　oradebugwakeup&Smon

　　undefineSmon

　　--求回退率

　　selectb.value/（a.value+b.value）,a.value,b.valuefromv$sysstata,v$sysstatb

　　wherea.statistic#=4andb.statistic#=5;

　　--求DISKREAD较多的SQL

　　selectst.sql_textfromv$sqls,v$sqltextst

　　wheres.address=st.addressands.hash_value=st.hash_valueands.disk_reads>300;

　　--求DISKSORT严重的SQL

　　selectsess.username,sql.sql_text,sort1.blocks

　　fromv$sessionsess,v$sqlareasql,v$sort_usagesort1

　　wheresess.serial#=sort1.session_num

　　andsort1.sqladdr=sql.address

　　andsort1.sqlhash=sql.hash_valueandsort1.blocks>200;

　　--求对象的创建代码

　　columncolumn_nameformata36

　　columnsql_textformata99

　　selectdbms_metadata.get_ddl（'TABLE','&1'）fromdual;

　　selectdbms_metadata.get_ddl（'INDEX','&1'）fromdual;

　　--求表的索引

　　setlinesize131

　　selecta.index_name,a.column_name,b.status,b.index_type

　　fromuser_ind_columnsa,user_indexesb

　　wherea.index_name=b.index_nameanda.table_name='&1';

　　求索引中行数较多的

　　selectindex_name,blevel,num_rows,CLUSTERING_FACTOR,statusfromuser_indexeswherenum_rows>10000andblevel>0

　　selecttable_name,index_name,blevel,num_rows,CLUSTERING_FACTOR,statusfromuser_indexeswherestatus<>'VALID'

    --求当前会话的SID，SERIAL#

　　selectsid,serial#fromv$sessionwhereaudsid=SYS_CONTEXT（'USERENV','SESSIONID'）;

　　--求表空间的未用空间

　　colmbytesformat9999.9999

　　selecttablespace_name,sum（bytes）/1024/1024mbytesfromdba_free_spacegroupbytablespace_name;

　　--求表中定义的触发器

　　selecttable_name,index_type,index_name,uniquenessfromuser_indexeswheretable_name='&1';

　　selecttrigger_namefromuser_triggerswheretable_name='&1';

　　--求未定义索引的表

　　selecttable_namefromuser_tableswheretable_namenotin（selecttable_namefromuser_ind_columns）;

　　--执行常用的过程

　　execprint_sql（'selectcount（*）fromtab'）;

　　execshow_space2（'table_name'）;

　　--求freememory

　　select*fromv$sgastatwherename='freememory';

　　selecta.name,sum（b.value）fromv$statnamea,v$sesstatbwherea.statistic#=b.statistic#groupbya.name;

　　查看一下谁在使用那个可以得回滚段，或者查看一下某个可以得用户在使用回滚段，找出领回滚段不断增长的事务，再看看如何处理它，是否可以将它commit，再不行就看能否kill它,查看当前正在使用的回滚段的用户信息和回滚段信息:

　　s