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

1、管理员日常工作中必备的sql列表数据库管理员日常工作中必备的sql列表-监控索引是否使用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$d

2、bfile dfwhere fs.file#=df.file# order 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(wa

3、it_time)from v$latch_childrengroup 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_cha

4、r(a.first_time,yyyy-mm-dd hh24:mi:ss) 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,us

5、er_ind_columns uicwhere ui.table_name=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 (sel

6、ect constraint_name from user_cons_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

7、user_constraints child,user_constraints 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

8、child.table_name =&table_nameorder 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

9、=&table_name-使用dbms_xplan生成一个执行计划explain 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.ob

10、ject_name,count(b.objd)from v$bh b,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,

11、wait_timefrom v$session where state 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-U

12、ser, s.programfrom v$process p, v$session 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.obj

13、ect_id=o.object_id and l.session_id=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#fro

14、m v$session s,v$lock l,dba_objects owhere l.sid = s.sid and l.id1 = o.object_id(+) and s.username is not null ;-求等待的事件及会话信息/求会话的等待及会话信息select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_waitfrom v$session s,v$session_event sewhere s.username is not null and se.sid=s.sid and s

15、.status=ACTIVE and se.event not like %SQL*Net% order by s.username;select s.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_waitfrom v$session s,v$session_wait swwhere s.username is not null and sw.sid=s.sid and sw.event not like %SQL*Net% order by s.username;-求会话等待的file_id/block_idcol e

16、vent format a24col p1text format a12col p2text format a12col p3text format a12select sid,event,p1text, p1, p2text, p2, p3text, p3from v$session_waitwhere event not like %SQL% and event not like %rdbms% and event not like %mon% order by event;select name,wait_time from v$latch l where exists (select

17、1 from (select sid,event,p1text, p1, p2text, p2, p3text, p3from v$session_waitwhere event not like %SQL% and event not like %rdbms% and event not like %mon%) x where x.p1= l.latch#);-求会话等待的对象col owner format a18col segment_name format a32col segment_type format a32select owner,segment_name,segment_t

18、ypefrom dba_extentswhere file_id = &file_id and &block_id between block_id and block_id + blocks - 1;-求buffer cache中的块信息select o.OBJECT_TYPE, substr(o.OBJECT_NAME,1,10) objname , b.objd , b.status, count(b.objd)from v$bh b, dba_objects owhere b.objd = o.data_object_id and o.owner = &1 group by o.obj

19、ect_type, o.object_name,b.objd, b.status ;-求日志文件的空间使用select le.leseq current_log_sequence#, 100*cp.cpodr_bno/le.lesiz percentage_fullfrom x$kcccp cp,x$kccle lewhere le.leseq =cp.cpodr_seq;-求等待中的对象select /*+rule */ s.sid, s.username, w.event, o.owner, o.segment_name, o.segment_type,o.partition_name,

20、w.seconds_in_wait seconds, w.statefrom v$session_wait w, v$session s, dba_extents owhere w.event in (select name from v$event_name where parameter1 = file#and parameter2 = block# and name not like control%)and o.owner sys and w.sid = s.sid and w.p1 = o.file_id and w.p2 = o.block_id and w.p2 300;-求DI

21、SK SORT严重的SQLselect sess.username, sql.sql_text, sort1.blocksfrom v$session sess, v$sqlarea sql, v$sort_usage sort1where sess.serial# = sort1.session_numand sort1.sqladdr = sql.addressand sort1.sqlhash = sql.hash_value and sort1.blocks 200;-求对象的创建代码column column_name format a36column sql_text format

22、 a99select dbms_metadata.get_ddl(TABLE,&1) from dual;select dbms_metadata.get_ddl(INDEX,&1) from dual;-求表的索引set linesize 131select a.index_name,a.column_name,b.status, b.index_typefrom user_ind_columns a,user_indexes bwhere a.index_name=b.index_name and a.table_name=&1;求索引中行数较多的select index_name,ble

23、vel,num_rows,CLUSTERING_FACTOR,status from user_indexes where num_rows 10000 and blevel 0select table_name,index_name,blevel,num_rows,CLUSTERING_FACTOR,status from user_indexes where status VALID -求当前会话的SID，SERIAL#select sid, serial# from v$session where audsid = SYS_CONTEXT(USERENV,SESSIONID);-求表空间

24、的未用空间col mbytes format 9999.9999select tablespace_name,sum(bytes)/1024/1024 mbytes from dba_free_space group by tablespace_name;-求表中定义的触发器select table_name,index_type,index_name,uniqueness from user_indexes where table_name=&1;select trigger_name from user_triggers where table_name=&1;-求未定义索引的表selec

25、t table_name from user_tables where table_name not in (select table_name from user_ind_columns);-执行常用的过程exec print_sql(select count(*) from tab);exec show_space2(table_name);-求free memoryselect * from v$sgastat where name=free memory;select a.name,sum(b.value) from v$statname a,v$sesstat b where a.statistic# = b.statistic# group by a.name;查看一下谁在使用那个可以得回滚段，或者查看一下某个可以得用户在使用回滚段，找出领回滚段不断增长的事务，再看看如何处理它，是否可以将它commit，再不行就看能否kill它,查看当前正在使用的回滚段的用户信息和回滚段信息:s