oracle 表空間關閉自增長 autoextend off

mhabbyo發表於2016-04-26
因為原來的一個磁碟已經佔據了 97% 的空間,隨時有可能磁碟被寫滿導致服務掛起,為最大限度不影響原來業務的情況下,
將所有能夠自增長的表空間的自增長特性關閉,將新的資料檔案全部寫到新新增的/u02 磁碟上

[root@BI-Database ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      275G   11G  251G   4% /
tmpfs                  63G   16G   48G  25% /dev/shm
/dev/sda1             190M   55M  126M  31% /boot
/dev/dfa1             3.0T  2.8T  118G  97% /u01
/dev/dfb              5.9T   34M  5.9T   1% /u02


1 檢視有哪些資料檔案是自增長的


SQL> SELECT T.TABLESPACE_NAME,D.FILE_NAME,
  2  D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
  3  FROM DBA_TABLESPACES T,DBA_DATA_FILES D
  4  WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
  5  and  D.AUTOEXTENSIBLE != 'NO'
  6  ORDER BY TABLESPACE_NAME,FILE_NAME
  7  ;
 
TABLESPACE_NAME                FILE_NAME                                                                        AUTOEXTENSIBLE      BYTES   MAXBYTES STATUS
------------------------------ -------------------------------------------------------------------------------- -------------- ---------- ---------- ---------
SYSAUX                         /u01/app/oracle/oradata/******/sysaux01.dbf                                          YES            4173332480 1374388879 AVAILABLE
SYSTEM                         /u01/app/oracle/oradata/******/system01.dbf                                          YES            1289748480 1374388879 AVAILABLE
UNDOTBS1                       /u01/app/oracle/oradata/******/undotbs01.dbf                                         YES            1374388879 1374388879 AVAILABLE
USERS                          /u01/app/oracle/oradata/******/users01.dbf                                           YES            3757309952 1374388879 AVAILABLE




2 關閉掉自增長,保持原有的磁碟空間不再增加


SQL> alter database datafile '/u01/app/oracle/oradata/******/sysaux01.dbf' autoextend off;
 
Database altered
SQL> alter database datafile '/u01/app/oracle/oradata/******/system01.dbf' autoextend off;
 
Database altered
SQL> alter database datafile '/u01/app/oracle/oradata/******/undotbs01.dbf' autoextend off;
 
Database altered
SQL> alter database datafile '/u01/app/oracle/oradata/******/users01.dbf' autoextend off;
 
Database altered




3 確認沒有自增長的表空間


SQL> SELECT T.TABLESPACE_NAME,D.FILE_NAME,
  2  D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
  3  FROM DBA_TABLESPACES T,DBA_DATA_FILES D
  4  WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
  5  and  D.AUTOEXTENSIBLE = 'YES'
  6  ORDER BY TABLESPACE_NAME,FILE_NAME
  7  ;
 
TABLESPACE_NAME                FILE_NAME                                                                        AUTOEXTENSIBLE      BYTES   MAXBYTES STATUS
------------------------------ -------------------------------------------------------------------------------- -------------- ---------- ---------- ---------
 


4 表空間超過 70% 的在新的磁碟上新增資料檔案


SQL> select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
  2  from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
  3  from dba_free_space
  4  group by tablespace_name) free,
  5  (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
  6  from dba_data_files
  7  group by tablespace_name) total
  8  where free.tablespace_name = total.tablespace_name;
  
  
 
TABLESPACE_NAME                  TOTAL_MB    USED_MB USED_PCT
------------------------------ ---------- ---------- -----------------------------------------
******_TEST                             30720    5918.75 19.27%
******_DW_16K                         849920  609492.44 71.71%
SYSAUX                               3980    3786.56 95.14%
UNDOTBS1                        242687.94    1625.75 .67%
******_DM_IDX                         102400   29779.56 29.08%
******_DW                             716800  509206.13 71.04%
USERS                             56312.5   12278.56 21.8%
SYSTEM                              11470    1607.94 14.02%
******_INFAREP                         10240    1479.44 14.45%
******_DM                             112640   58144.25 51.62%
UTL_TBS                             10240       3.94 .04%
******_STG                             51200    2390.31 4.67%
******_DW_IDX                         215040  125395.25 58.31%
******_ODS_IDX                         10240    2423.19 23.66%
******_ODS                             10240    8655.56 84.53%
******_MONITOR_TBS                    215040   41819.75 19.45%
******_RECON_TBS                       10240    4632.31 45.24%
 
17 rows selected


ALTER TABLESPACE SYSAUX ADD DATAFILE      '/u02/oradata/******/SYSAUX02.dbf' size 5G;
ALTER TABLESPACE ******_ODS ADD DATAFILE     '/u02/oradata/******/******_ODS_02.dbf' size 10G;
ALTER TABLESPACE ******_DW_16K ADD DATAFILE  '/u02/oradata/******/******_dw_16k_16.dbf' size 50G;
ALTER TABLESPACE ******_DW ADD DATAFILE      '/u02/oradata/******/******_dw_11.dbf' size 50G;


SQL> ALTER TABLESPACE SYSAUX ADD DATAFILE      '/u02/oradata/******/SYSAUX02.dbf' size 5G;
 
Tablespace altered
 
SQL> ALTER TABLESPACE ******_ODS ADD DATAFILE     '/u02/oradata/******/******_ODS_02.dbf' size 10G;
 
Tablespace altered
 
SQL> ALTER TABLESPACE ******_DW_16K ADD DATAFILE  '/u02/oradata/******/******_dw_16k_16.dbf' size 50G;
 
Tablespace altered
 
SQL> ALTER TABLESPACE ******_DW ADD DATAFILE      '/u02/oradata/******/******_dw_11.dbf' size 50G;
 
Tablespace altered




新增後的表空間的使用


select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;




TABLESPACE_NAME                  TOTAL_MB    USED_MB USED_PCT
------------------------------ ---------- ---------- -----------------------------------------
******_TEST                             30720    5918.75 19.27%
******_DW_16K                         901120  609493.44 67.64%
SYSAUX                               9100    3787.56 41.62%
UNDOTBS1                        242687.94    1721.75 .71%
******_DM_IDX                         102400   29779.56 29.08%
******_DW                             768000  509207.13 66.3%
USERS                             56312.5   12278.56 21.8%
SYSTEM                              11470    1607.94 14.02%
******_INFAREP                         10240    1479.44 14.45%
******_DM                             112640   58144.25 51.62%
UTL_TBS                             10240       3.94 .04%
******_STG                             51200    2390.31 4.67%
******_DW_IDX                         215040  125395.25 58.31%
******_ODS_IDX                         10240    2423.19 23.66%
******_ODS                             20480    8656.56 42.27%
******_MONITOR_TBS                    215040   41819.75 19.45%
******_RECON_TBS                       10240    4632.31 45.24%
 
17 rows selected


磁碟空間的分佈


[root@******-Database ******]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      275G   11G  251G   4% /
tmpfs                  63G   16G   48G  25% /dev/shm
/dev/sda1             190M   55M  126M  31% /boot
/dev/dfa1             3.0T  2.8T  118G  97% /u01
/dev/dfb              5.9T  116G  5.8T   2% /u02

從此不用擔心以後會出現/u01 被寫爆的極端情況出現

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29291882/viewspace-2088336/,如需轉載,請註明出處,否則將追究法律責任。

相關文章