日常用的sql列表(摘抄)

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

--监控索引是否使用
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'andowner='&'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_nameand1<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>