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

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

2013-11-13 12:34:00  來源: SQL語言 

  求會話等待的file_id/block_id

  col event format a
  col ptext format a
  col ptext format a
  col ptext format a
  select sideventptext p ptext p ptext p
  from v$session_wait
  where event not like %SQL% and event not like %rdbms% and event not like %mon% order by event;
  select namewait_time from v$latch l where exists (select from (select sideventptext p ptext p ptext p
  from v$session_wait
  where event not like %SQL% and event not like %rdbms% and event not like %mon%
  ) x where xp= llatch#);

  求會話等待的對象

  col owner format a
  col segment_name format a
  col segment_type format a
  select ownersegment_namesegment_type
  from dba_extents
  where file_id = &file_id and &block_id between block_id and block_id + blocks ;

  求buffer cache中的塊信息

  select oOBJECT_TYPE substr(oOBJECT_NAME) objname bobjd bstatus count(bobjd)
  from v$bh b dba_objects o
  where bobjd = odata_object_id and oowner = & group by oobject_type oobject_namebobjd bstatus ;

  求日志文件的空間使用

  select leleseq current_log_sequence# *cpcpodr_bno/lelesiz percentage_full
  from x$kcccp cpx$kccle le
  where leleseq =cpcpodr_seq;

  求等待中的對象

  select /*+rule */ ssid susername wevent oowner osegment_name osegment_type
  opartition_name wseconds_in_wait seconds wstate
  from v$session_wait w v$session s dba_extents o
  where wevent in (select name from v$event_name where parameter = file#
  and parameter = block# and name not like control%)
  and oowner <> sys and wsid = ssid and wp = ofile_id and wp >= oblock_id and wp < oblock_id + oblocks

  求當前事務的重做尺寸

  select value
  from v$mystat v$statname
  where v$mystatstatistic# = v$statnamestatistic# and v$statnamename = redo size;

  喚醒smon去清除臨時段

  column pid new_value Smon
  set termout off
  select ppid from sysv_$bgprocess bsysv_$process p where bname = SMON and paddr = bpaddr
  /
  set termout on
  oradebug wakeup &Smon
  undefine Smon

  求回退率

  select bvalue/(avalue + bvalue)avaluebvalue from v$sysstat av$sysstat b
  where astatistic#= and bstatistic#=;

  求DISK READ較多的SQL

  select stsql_text from v$sql sv$sqltext st
  where saddress=staddress and shash_value=sthash_value and sdisk_reads > ;

  求DISK SORT嚴重的SQL

  select sessusername sqlsql_text sortblocks
  from v$session sess v$sqlarea sql v$sort_usage sort
  where sessserial# = sortsession_num
  and sortsqladdr = sqladdress
  and sortsqlhash = sqlhash_value and sortblocks > ;

  求對象的創建代碼

  column column_name format a
  column sql_text format a
  select dbms_metadataget_ddl(TABLE&) from dual;
  select dbms_metadataget_ddl(INDEX&) from dual;

  求表的索引

  set linesize
  select aindex_nameacolumn_namebstatus bindex_type
  from user_ind_columns auser_indexes b
  where aindex_name=bindex_name and atable_name=&;

  求索引中行數較多的

  select index_nameblevelnum_rowsCLUSTERING_FACTORstatus from user_indexes where num_rows > and blevel >
  select table_nameindex_nameblevelnum_rowsCLUSTERING_FACTORstatus from user_indexes where status <> VALID

  求當前會話的SIDSERIAL#

  select sid serial# from v$session where audsid = SYS_CONTEXT(USERENVSESSIONID);

  求表空間的未用空間

  col mbytes format
  select tablespace_namesum(bytes)// mbytes from dba_free_space group by tablespace_name;

  求表中定義的觸發器

  select table_nameindex_typeindex_nameuniqueness from user_indexes where table_name=&;
  select trigger_name from user_triggers where table_name=&;

  求未定義索引的表

  select 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_space(table_name);

  求free memory

  select * from v$sgastat where name=free memory;
  select anamesum(bvalue) from v$statname av$sesstat b where astatistic# = bstatistic# group by aname;

  查看一下誰在使用那個可以得回滾段或者查看一下某個可以得用戶在使用回滾段找出領回滾段不斷增長的事務再看看如何處理它是否可以將它commit再不行就看能否kill它查看當前正在使用的回滾段的用戶信息和回滾段信息:

  set linesize
  SELECT rname ROLLBACK SEGMENT NAME lsid ORACLE PIDpspid SYSTEM PID susername ORACLE USERNAME
  FROM v$lock l v$process p v$rollname r v$session s
  WHERE lsid = ppid(+) AND ssid=lsid AND TRUNC(lid(+)/) = rusn AND ltype(+) = TX AND llmode(+) = ORDER BY rname;

[]  []  []  


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