《OracleDBA管理脚本》由会员分享，可在线阅读，更多相关《OracleDBA管理脚本（24页珍藏版）》请在金锄头文库上搜索。
1、-监控索引是否使用alter index &index_name monitoring usage;alter index &index_name nomonitoring usage;select * from v$object_usage where index_name = &index_name;-求数据文件的I/O分布select df.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetimfrom v$filestat fs,v$dbfile dfwhere fs.file#=df.file# ord
2、er by df.name;-求某个隐藏参数的值col ksppinm format a54col ksppstvl format a54select ksppinm, ksppstvlfrom x$ksppi pi, x$ksppcv cvwhere cv.indx=pi.indx and pi.ksppinm like _% escape and pi.ksppinm like %meer%;-求系统中较大的latchselect name,sum(gets),sum(misses),sum(sleeps),sum(wait_time)from v$latch_childrengroup
3、by name having sum(gets) 50 order by 2;-求归档日志的切换频率(生产系统可能时间会很长)select start_recid,start_time,end_recid,end_time,minutes from (select test.*, rownum as rnfrom (select b.recid start_recid,to_char(b.first_time,yyyy-mm-dd hh24:mi:ss) start_time,a.recid end_recid,to_char(a.first_time,yyyy-mm-dd hh24:mi:s
4、s) end_time,round(a.first_time-b.first_time)*24)*60,2) minutesfrom v$log_history a,v$log_history b where a.recid=b.recid+1 and b.first_time sysdate - 1order by a.first_time desc) test) y where y.rn 0;-求表的索引信息select ui.table_name,ui.index_namefrom user_indexes ui,user_ind_columns uicwhere ui.table_na
5、me=uic.table_name and ui.index_name=uic.index_nameand ui.table_name like &table_name% and uic.column_name=&column_name;-显示表的外键信息col search_condition format a54select table_name,constraint_namefrom user_constraintswhere constraint_type =R and constraint_name in (select constraint_name from user_cons_
6、columns where column_name=&1);select rpad(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_namefrom user_constraints child,user_constra
7、ints parent,user_cons_columns cp,user_cons_columns pcwhere child.constraint_type = R and child.r_constraint_name = parent.constraint_name andchild.constraint_name = cp.constraint_name and parent.constraint_name = pc.constraint_name andcp.position = pc.position and child.table_name =&table_nameorder
8、by child.owner,child.table_name,child.constraint_name,cp.position;-显示表的分区及子分区(user_tab_subpartitions)col table_name format a16col partition_name format a16col high_value format a81select table_name,partition_name,HIGH_VALUE from user_tab_partitions where table_name=&table_name-使用dbms_xplan生成一个执行计划ex
9、plain plan set statement_id = &sql_id for &sql;select * from table(dbms_xplan.display);-求某个事务的重做信息(bytes)select s.name,m.valuefrom v$mystat m,v$statname swhere m.statistic#=s.statistic# and s.name like %redo size%;-求cache中缓存超过其5%的对象select o.owner,o.object_type,o.object_name,count(b.objd)from v$bh b,
10、dba_objects owhere b.objd = o.object_idgroup by o.owner,o.object_type,o.object_namehaving count(b.objd) (select to_number(value)*0.05 from v$parameter where name = db_block_buffers);-求谁阻塞了某个session(10g)select sid, username, event, blocking_session,seconds_in_wait, wait_timefrom v$session where state
11、 in (WAITING) and wait_class != Idle;-求session的OS进程IDcol program format a54select p.spid OS Thread, b.name Name-User, s.programfrom v$process p, v$session s, v$bgprocess bwhere p.addr = s.paddr and p.addr = b.paddrUNION ALLselect p.spid OS Thread, s.username Name-User, s.programfrom v$process p, v$s
12、ession s where p.addr = s.paddr and s.username is not null;-查会话的阻塞col user_name format a32select /*+ rule */ lpad( ,decode(l.xidusn ,0,3,0)|l.oracle_username user_name, o.owner,o.object_name,s.sid,s.serial#from v$locked_object l,dba_objects o,v$session swhere l.object_id=o.object_id and l.session_id
13、=s.sid order by o.object_id,xidusn desc ;col username format a15col lock_level format a8col owner format a18col object_name format a32select /*+ rule */ s.username, decode(l.type,tm,table lock, tx,row lock, null) lock_level, o.owner,o.object_name,s.sid,s.serial#from v$session s,v$lock l,dba_objects owhere l.sid = s.sid and l.id1 = o.object_id(+) an
