管理员日常工作中必备的sql列表.docx
- 文档编号:6189967
- 上传时间:2023-01-04
- 格式:DOCX
- 页数:52
- 大小:41.03KB
管理员日常工作中必备的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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 管理员 日常工作 必备 sql 列表