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

Oracle維護常用SQL語句匯總

2013-11-13 15:33:54  來源: Oracle 

  如何遠程判斷Oracle數據庫的安裝平台

  select * from v$version;

  查看表空間的使用情況

  select sum(bytes)/(*) as free_spacetablespace_name

  from dba_free_space

  group by tablespace_name;

  SELECT ATABLESPACE_NAMEABYTES TOTALBBYTES USED CBYTES FREE

  (BBYTES*)/ABYTES % USED(CBYTES*)/ABYTES % FREE

  FROM SYSSM$TS_AVAIL ASYSSM$TS_USED BSYSSM$TS_FREE C

  WHERE ATABLESPACE_NAME=BTABLESPACE_NAME AND ATABLESPACE_NAME=CTABLESPACE_NAME;

  查看表空間的名稱及大小

  select ttablespace_name round(sum(bytes/(*))) ts_size

  from dba_tablespaces t dba_data_files d

  where ttablespace_name = dtablespace_name

  group by ttablespace_name;

  查看表空間物理文件的名稱及大小

  select tablespace_name file_id file_name

  round(bytes/(*)) total_space

  from dba_data_files

  order by tablespace_name;

  查看回滾段名稱及大小

  select segment_name tablespace_name rstatus

  (initial_extent/) InitialExtent(next_extent/) NextExtent

  max_extents vcurext CurExtent

  From dba_rollback_segs r v$rollstat v

  Where rsegment_id = vusn(+)

  order by segment_name ;

  查看控制文件

  select name from v$controlfile;

  查看日志文件

  select member from v$logfile;

  查看表空間的使用情況

  select sum(bytes)/(*) as free_spacetablespace_name

  from dba_free_space

  group by tablespace_name;

  SELECT ATABLESPACE_NAMEABYTES TOTALBBYTES USED CBYTES FREE

  (BBYTES*)/ABYTES % USED(CBYTES*)/ABYTES % FREE

  FROM SYSSM$TS_AVAIL ASYSSM$TS_USED BSYSSM$TS_FREE C

  WHERE ATABLESPACE_NAME=BTABLESPACE_NAME AND ATABLESPACE_NAME=CTABLESPACE_NAME;

  查看數據庫庫對象

  select owner object_type status count(*) count# from all_objects group by owner object_type status;

  查看數據庫的版本

  Select version FROM Product_component_version

  Where SUBSTR(PRODUCT)=Oracle;

  查看數據庫的創建日期和歸檔方式

  Select Created Log_Mode Log_Mode From V$Database;

  如何遠程判斷Oracle數據庫的安裝平台

  select * from v$version;

  查看數據表的參數信息

  SELECT partition_name high_value high_value_length tablespace_name

  pct_free pct_used ini_trans max_trans initial_extent

  next_extent min_extent max_extent pct_increase FREELISTS

  freelist_groups LOGGING BUFFER_POOL num_rows blocks

  empty_blocks avg_space chain_cnt avg_row_len sample_size

  last_analyzed

  FROM dba_tab_partitions

  WHERE table_name = :tname AND table_owner = :towner

  ORDER BY partition_position

  查看還沒提交的事務

  select * from v$locked_object;

  select * from v$transaction;

  回滾段查看

  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

  捕捉運行很久的SQL

  column username format a

  column opname format a

  column progress format a

  select usernamesidopname

  round(sofar* / totalwork) || % as progress

  time_remainingsql_text

  from v$session_longops v$sql

  where time_remaining <>

  and sql_address = address

  and sql_hash_value = hash_value

  /

  查看數據表的參數信息

  SELECT   partition_name high_value high_value_length tablespace_name

  pct_free pct_used ini_trans max_trans initial_extent

  next_extent min_extent max_extent pct_increase FREELISTS

  freelist_groups LOGGING BUFFER_POOL num_rows blocks

  empty_blocks avg_space chain_cnt avg_row_len sample_size

  last_analyzed

  FROM dba_tab_partitions

  WHERE table_name = :tname AND table_owner = :towner

  ORDER BY partition_position

  查找object為哪些進程所用

  select

  pspid

  ssid

  sserial# serial_num

  susername user_name

  atype object_type

  sosuser os_user_name

  aowner

  aobject object_name

  decode(sign( command)

  

  to_char(command) Action Code # || to_char(command) ) action

  pprogram oracle_process

  sterminal terminal

  sprogram program

  sstatus session_status

  from v$session s v$access a v$process p

  where spaddr = paddr and

  stype = USER and

  asid = ssid   and

  aobject=SUBSCRIBER_ATTR

  order by susername sosuser

  耗資源的進程(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

  根據sid查是哪台電腦的鏈接

  column osuser format a

  column username format a

  column machine format a

  select osusermachineusernamesidserial# from v$session where sid=;

  根據sid查對應的sql

  select SIDSQL_TEXT from v$open_cursor where SID=;

  查看等待(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_idbytesblockssegment_name from dba_extents;

  select * from ts_blocks_v;

  select tablespace_namesum(bytes)max(bytes)count(block_id) from dba_free_space

  group by tablespace_name;

  查詢有哪些數據庫實例在運行

  select inst_name from v$active_instances;

   查找oracle性能瓶頸sql

  select sql_textspidv$sessionprogramprocess from

  v$sqlareav$sessionv$process

  where v$sqlareaaddress=v$sessionsql_address

  and v$sqlareahash_value=v$sessionsql_hash_value

  and v$sessionpaddr=v$processaddr

  and v$processspid in (操作系統PID);

  select sideventpptext from v$session_wait;

   找出最耗資源的sql

  select * from v$process where spid=;

  select sql_hash_valuemachineusernameprogram from v$session where PAddr=BA;

  select * from v$sqltext where hash_value=;

  select * from v$sql where hash_value=;

  select * from v$sqlarea where hash_value=;

  SELECT ausernameamachineaprogramasidaserial#astatuscpiececsql_text FROM v$session av$process bv$sqltext c WHERE bspid= AND baddr=apaddr AND asql_address=caddress(+) ORDER BY cpiece

  要找出最耗資源的sql我們可以首先使用top等工具找到最好資源的進程(記住進程號)例如操作系統進程號為然後根據這個進程號(v$processspid)在v$process中找到進程地址(v$processaddr)然後根據這個地址在 v$session中找到相應的sid(v$sessionsid)然後根據這個sid找到相應的hash alue(v$session sql_hash_value)然後根據這個hash alue在v$sqltext$sqlv$sqlarea等視圖中找到對應的sql語句(sql_text)

  select * from v$process where spid=;

  select sql_hash_valuemachineusernameprogram from v$session where PAddr=BA;

  select * from v$sqltext where hash_value=;

  select * from v$sql where hash_value=;

  select * from v$sqlarea where hash_value=;

  SELECT ausernameamachineaprogramasidaserial#astatuscpiececsql_text FROM v$session av$process bv$sqltext c WHERE bspid= AND baddr=apaddr AND asql_address=caddress(+) ORDER BY cpiece


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