熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL語言 >> 正文

數據庫進階:數據庫管理員日常工作中必備的sql列表[1]

2013-11-13 12:34:00  來源: 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 dfnamephyrdsphywrtsphyblkrdphyblkwrtsingleblkrdsreadtimwritetim
  from v$filestat fsv$dbfile df
  where fsfile#=dffile# order by dfname;

  求某個隱藏參數的值

  col ksppinm format a
  col ksppstvl format a
  select ksppinm ksppstvl
  from x$ksppi pi x$ksppcv cv
  where cvindx=piindx and piksppinm like \_% escape \ and piksppinm like %meer%;

  求系統中較大的latch

  select namesum(gets)sum(misses)sum(sleeps)sum(wait_time)
  from v$latch_children
  group by name having sum(gets) > order by ;

  求歸檔日志的切換頻率(生產系統可能時間會很長)

  select start_recidstart_timeend_recidend_timeminutes from (select test* rownum as rn
  from (select brecid start_recidto_char(bfirst_timeyyyymmdd hh:mi:ss) start_time
  arecid end_recidto_char(afirst_timeyyyymmdd hh:mi:ss) end_timeround(((afirst_timebfirst_time)*)*) minutes
  from v$log_history av$log_history b where arecid=brecid+ and bfirst_time > sysdate
  order by afirst_time desc) test) y where yrn <

  求回滾段正在處理的事務

  select anamebxactscsidcserial#dsql_text
  from v$rollname av$rollstat bv$session cv$sqltext dv$transaction e
  where ausn=busn and busn=exidusn and ctaddr=eaddr
  and csql_address=daddress and csql_hash_value=dhash_value order by anamecsiddpiece;

  求出無效的對象

  select alter procedure ||object_name|| compile;
  from dba_objects
  where status=INVALID and wner=& and object_type in (PACKAGEPACKAGE BODY);
  /
  select ownerobject_nameobject_typestatus from dba_objects where status=INVALID;

  求process/session的狀態

  select ppidpspidsprogramssidsserial#
  from v$process pv$session s where spaddr=paddr;

  求當前session的狀態

  select snnamemsvalue
  from v$mystat msv$statname sn
  where msstatistic#=snstatistic# and msvalue > ;

  求表的索引信息

  select uitable_nameuiindex_name
  from user_indexes uiuser_ind_columns uic
  where uitable_name=uictable_name and uiindex_name=uicindex_name
  and uitable_name like &table_name% and uiccolumn_name=&column_name;

  顯示表的外鍵信息

  col search_condition format a
  select table_nameconstraint_name
  from user_constraints
  where constraint_type =R and constraint_name in (select constraint_name from user_cons_columns where column_name=&);
  select rpad(childtable_name ) child_tablename
  rpad(cpcolumn_name ) referring_columnrpad(parenttable_name ) parent_tablename
  rpad(pccolumn_name ) referred_columnrpad(childconstraint_name ) constraint_name
  from user_constraints childuser_constraints parent
  user_cons_columns cpuser_cons_columns pc
  where childconstraint_type = R and childr_constraint_name = parentconstraint_name and
  childconstraint_name = cpconstraint_name and parentconstraint_name = pcconstraint_name and
  cpposition = pcposition and childtable_name =&table_name
  order by childownerchildtable_namechildconstraint_namecpposition;

  顯示表的分區及子分區(user_tab_subpartitions)

  col table_name format a
  col partition_name format a
  col high_value format a
  select table_namepartition_nameHIGH_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_xplandisplay);

  求某個事務的重做信息(bytes)

  select snamemvalue
  from v$mystat mv$statname s
  where mstatistic#=sstatistic# and sname like %redo size%;

  求cache中緩存超過其%的對象

  select oowneroobject_typeoobject_namecount(bobjd)
  from v$bh bdba_objects o
  where bobjd = oobject_id
  group by oowneroobject_typeoobject_name
  having count(bobjd) > (select to_number(value)* from v$parameter where name = db_block_buffers);

  求誰阻塞了某個session(g)

  select sid username event blocking_session
  seconds_in_wait wait_time
  from v$session where state in (WAITING) and wait_class != Idle;

  求session的OS進程ID

  col program format a
  select pspid OS Thread bname NameUser sprogram
  from v$process p v$session s v$bgprocess b
  where paddr = spaddr and paddr = bpaddr
  UNION ALL
  select pspid OS Thread susername NameUser sprogram
  from v$process p v$session s where paddr = spaddr and susername is not null;

  查會話的阻塞

  col user_name format a
  select /*+ rule */ lpad( decode(lxidusn ))||loracle_username user_name oowneroobject_namessidsserial#
  from v$locked_object ldba_objects ov$session s
  where lobject_id=oobject_id and lsession_id=ssid order by oobject_idxidusn desc ;
  col username format a
  col lock_level format a
  col owner format a
  col object_name format a
  select /*+ rule */ susername decode(ltypetmtable lock txrow lock null) lock_level oowneroobject_namessidsserial#
  from v$session sv$lock ldba_objects o
  where lsid = ssid and lid = oobject_id(+) and susername is not null ;

  求等待的事件及會話信息/求會話的等待及會話信息

  select sesidsusernameseeventsetotal_waitssetime_waitedseaverage_wait
  from v$session sv$session_event se
  where susername is not null and sesid=ssid and sstatus=ACTIVE and seevent not like %SQL*Net% order by susername;
  select ssidsusernamesweventswwait_timeswstateswseconds_in_wait
  from v$session sv$session_wait sw
  where susername is not null and swsid=ssid and swevent not like %SQL*Net% order by susername;

[]  []  []  


From:http://tw.wingwit.com/Article/program/SQL/201311/16149.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.