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

Oracle 數據庫表空間容量調整腳本

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

  (表空間縮容腳本)]

  獲取需要釋放空間的表空間信息(包含oracle database自有表空間)

  drop table systemtbs_detail;

  create table systemtbs_detail as select

  atablespace_name

  abytes// "Sum_MB"

  (abytesbbytes)// "used_MB"

  bbytes// "free_MB"

  round(((abytesbbytes)/abytes)*) "percent_used"

  from

  (select tablespace_namesum(bytes) bytes from dba_data_files group by tablespace_name) a

  (select tablespace_namesum(bytes) bytesmax(bytes) largest from dba_free_space group by tablespace_name) b

  where atablespace_name=btablespace_name

  order by ((abytesbbytes)/abytes) desc;

  select * from systemtbs_detail order by "Sum_MB" desc"free_MB" desc;

  獲取需要釋放空間的應用表空間數據文件使用情況

  drop table systemdatafile_space;

  create table systemdatafile_space as

  select aTABLESPACE_NAME

  aFILE_NAME

  aBYTES / / total

  bsum_free / / free

  from dba_data_files a

  (select file_id sum(bytes) sum_free

  from dba_free_space

  group by file_id) b

  where aFILE_ID = bfile_id

  and aTABLESPACE_NAME in (select tablespace_name

  from systemtbs_detail

  where (tablespace_name like %CQLT% or

  tablespace_name like %CQST%

  or tablespace_name like TS% or tablespace_name like IDX%

  or tablespace_name like %HX%)

  and "Sum_MB" > );

  select * from systemdatafile_space;

  生成數據文件大小重置腳本在每個數據文件當前實際使用空間大小基礎上增加 m 空間

  select alter database datafile || file_name || resize ||

  round(to_number(total free + )) || M;

  from systemdatafile_space;

  查看 ASM 磁盤組使用情況

  sqlplus / as sysdba <

  set feed off

  set linesize

  set pagesize

  set echo off

  spool /home/oracle/check_log/chktbslog append

  select namestatetypetotal_mbfree_mb from v$asm_diskgroup;

  spool off

  quit

  EOF


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