《管理员日常工作中必备的sql列表》由会员分享，可在线阅读，更多相关《管理员日常工作中必备的sql列表（13页珍藏版）》请在金锄头文库上搜索。
1、数据库管理员日常工作中必备的 sql 列表今天执行最多的 SQL 语句是什么？select sql_id,count(*),round(count(*)/sum(count(*) over (),2) pctfrom v$active_session_historywhere sample_timesysdate-1and session_type 50 order by 2;-求归档日志的切换频率( 生产系统可能时间会很长)select start_recid,start_time,end_recid,end_time,minutes from (select test.*, rownum
2、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: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
3、- 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_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
4、 format a54select table_name,constraint_namefrom user_constraintswhere constraint_type =R and constraint_name in (select 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,2
5、5, ) parent_tablename,rpad(pc.column_name,15, ) referred_column,rpad(child.constraint_name,25, ) constraint_namefrom 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.c
6、onstraint_name = cp.constraint_name and parent.constraint_name = pc.constraint_name andcp.position = pc.position and 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 for
7、mat a16col high_value format a81select table_name,partition_name,HIGH_VALUE from user_tab_partitions where table_name=&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$st
8、atname 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,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$pa
9、rameter where name = db_block_buffers);-求谁阻塞了某个 session(10g)select sid, username, event, blocking_session,seconds_in_wait, 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$pro
10、cess 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$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,
11、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=s.sid order by o.object_id,xidusn desc ;col username format a15col lock_level format a8col owner format a18col object_name format a32sel
12、ect /*+ 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(+) and s.username is not null ;-求等待的事件及会话信息/求会话的等待及会话信息select se.sid,s.username,se.event,se.
13、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.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
14、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 event 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 %S
15、QL% and event not like %rdbms% and event not like %mon% order by event;select name,wait_time from v$latch l where exists (select 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=
16、 l.latch#);-求会话等待的对象col owner format a18col segment_name format a32col segment_type format a32select owner,segment_name,segment_typefrom 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 owher
