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

數據庫手冊:Oracle維護常用SQL語句二

2013-11-13 22:13:25  來源: Oracle 

  回滾段查看
  
  select rownum sysdba_rollback_segssegment_name Name v$rollstatextents
  
  Extents v$rollstatrssize Size_in_Bytes v$rollstatxacts XActs
  
  v$rollstatgets Gets v$rollstatwaits Waits v$rollstatwrites Writes
  
  sysdba_rollback_segsstatus status from v$rollstat sysdba_rollback_segs
  
  v$rollname where v$rollnamename(+) = sysdba_rollback_segssegment_name and
  
  v$rollstatusn (+) = v$rollnameusn order by rownum
  
  耗資源的進程(top session)
  
  select sschemaname schema_name decode(sign( command)
  
  to_char(command) Action Code # || to_char(command) ) action status
  
  session_status sosuser os_user_name ssid pspid sserial# serial_num
  
  nvl(susername [Oracle process]) user_name sterminal terminal
  
  sprogram program stvalue criteria_value from v$sesstat st v$session s v$process p
  
  where stsid = ssid and ststatistic# = to_number() and (ALL = ALL
  
  or sstatus = ALL) and paddr = spaddr order by stvalue desc pspid asc susername asc sosuser asc
  
  查看鎖(lock)情況
  
  select /*+ RULE */ lsosuser os_user_name lsusername user_name
  
  decode(lstype RW Row wait enqueue lock TM DML enqueue lock TX
  
  Transaction enqueue lock UL User supplied lock) lock_type
  
  oobject_name object decode(lslmode null Row Share
  
  Row Exclusive Share Share Row Exclusive Exclusive null)
  
  lock_mode oowner lssid lsserial# serial_num lsid lsid
  
  from sysdba_objects o ( select sosuser susername ltype
  
  llmode ssid sserial# lid lid from v$session s
  
  v$lock l where ssid = lsid ) ls where oobject_id = lsid and oowner
  
  <> SYS order by oowner oobject_name
  
  查看等待(wait)情況
  
  SELECT v$waitstatclass v$unt count SUM(v$sysstatvalue) sum_value
  
  FROM v$waitstat v$sysstat WHERE v$sysstatname IN (db block gets
  
  consistent gets) group by v$waitstatclass v$unt
  
  查看sga情況
  
  SELECT NAME BYTES FROM SYSV_$SGASTAT ORDER BY NAME ASC
  
  查看catched object
  
  SELECT owner name db_link namespace
  
  type sharable_mem loads executions
  
  locks pins kept FROM v$db_object_cache
  
  查看V$SQLAREA
  
  SELECT SQL_TEXT SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS
  
  VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING EXECUTIONS
  
  USERS_EXECUTING LOADS FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS
  
  BUFFER_GETS ROWS_PROCESSED FROM V$SQLAREA
  
  查看object分類數量
  
  select decode (otype#INDEX TABLE CLUSTER VIEW
  
  SYNONYM SEQUENCE OTHER ) object_type count(*) quantity from
  
  sysobj$ o where otype# > group by decode (otype#INDEX TABLE
  
   CLUSTER VIEW SYNONYM SEQUENCE OTHER ) union select
  
  COLUMN count(*) from l$ union select DB LINK count(*) from
  
  按用戶查看object種類
  
  select uname schema sum(decode(otype# NULL)) indexes
  
  sum(decode(otype# NULL)) tables sum(decode(otype# NULL))
  
  clusters sum(decode(otype# NULL)) views sum(decode(otype#
  
  NULL)) synonyms sum(decode(otype# NULL)) sequences
  
  sum(decode(otype# NULL NULL NULL NULL NULL NULL ))
  
  others from sysobj$ o sysuser$ u where otype# >= and uuser# =
  
  oowner# and uname <> PUBLIC group by uname order by
  
  syslink$ union select CONSTRAINT count(*) from n$
  
  有關connection的相關信息
  
  )查看有哪些用戶連接
  
  select sosuser os_user_name decode(sign( command) to_char(command)
  
  Action Code # || to_char(command) ) action pprogram oracle_process
  
  status session_status sterminal terminal sprogram program
  
  susername user_name sfixed_table_sequence activity_meter query
  
   memory max_memory cpu_usage ssid sserial# serial_num
  
  from v$session s v$process p where spaddr=paddr and stype = USER
  
  order by susername sosuser
  
  )根據vsid查看對應連接的資源占用等情況
  
  select nname
  
  vvalue
  
  nclass
  
  nstatistic#
  
  from v$statname n
  
  v$sesstat v
  
  where vsid = and
  
  vstatistic# = nstatistic#
  
  order by nclass nstatistic#
  
  )根據sid查看對應連接正在運行的sql
  
  select /*+ PUSH_SUBQ */
  
  command_type
  
  sql_text
  
  sharable_mem
  
  persistent_mem
  
  runtime_mem
  
  sorts
  
  version_count
  
  loaded_versions
  
  open_versions
  
  users_opening
  
  executions
  
  users_executing
  
  loads
  
  first_load_time
  
  invalidations
  
  parse_calls
  
  disk_reads
  
  buffer_gets
  
  rows_processed
  
  sysdate start_time
  
  sysdate finish_time
  
  > || address sql_address
  
  N status
  
  from v$sqlarea
  
  where address = (select sql_address from v$session where sid = )
  
  查詢表空間使用情況select atablespace_name 表空間名稱
  
  round((nvl(bbytes_free)/abytes_alloc)*) 占用率(%)
  
  round(abytes_alloc//) 容量(M)
  
  round(nvl(bbytes_free)//) 空閒(M)
  
  round((abytes_allocnvl(bbytes_free))//) 使用(M)
  
  Largest 最大擴展段(M)
  
  to_char(sysdateyyyymmdd hh:mi:ss) 采樣時間
  
  from (select ftablespace_name
  
  sum(fbytes) bytes_alloc
  
  sum(decode(fautoextensibleYESfmaxbytesNOfbytes)) maxbytes
  
  from dba_data_files f
  
  group by tablespace_name) a
  
  (select ftablespace_name
  
  sum(fbytes) bytes_free
  
  from dba_free_space f
  
  group by tablespace_name) b
  
  (select round(max(fflength)*/) Largest
  
  tsname tablespace_name
  
  from sysfet$ ff sysfile$ tfsysts$ ts
  
  where tsts#=ffts# and fffile#=tfrelfile# and tsts#=tfts#
  
  group by tsname tfblocks) c
  
  where atablespace_name = btablespace_name and atablespace_name = ctablespace_name
  
   查詢表空間的碎片程度
  
  select tablespace_namecount(tablespace_name) from dba_free_space group by tablespace_name
  
  having count(tablespace_name)>;
  
  alter tablespace name coalesce;
  
  alter table name deallocate unused;
  
  create or replace view ts_blocks_v as
  
  select tablespace_nameblock_idbytesblocksfree space segment_name from dba_free_space
  
  union all
  
  select tablespace_nameblock_idbytesblockss
From:http://tw.wingwit.com/Article/program/Oracle/201311/18531.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.