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

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

2013-11-13 22:16:12  來源: Oracle 

  查看表空間的名稱及大小
  
  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;
  
  捕捉運行很久的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
  
  查看還沒提交的事務
  
  select * from v$locked_object;
  
  select * from v$transaction;
  
  查找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
From:http://tw.wingwit.com/Article/program/Oracle/201311/18644.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.