oracle 表空間關閉自增長 autoextend off
因為原來的一個磁碟已經佔據了 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 被寫爆的極端情況出現
將所有能夠自增長的表空間的自增長特性關閉，將新的資料檔案全部寫到新新增的/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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle表空間增長趨勢分析Oracle
- Oracle表空間相關操作Oracle
- oracle臨時表空間相關Oracle
- Linux 自動增加oracle 表空間LinuxOracle
- oracle 10g SYSAUX表空間快速增長之WRH$_SQL_PLAN篇Oracle 10gUXSQL
- oracle 臨時表空間的增刪改查Oracle
- ORACLE 臨時表空間的增刪改查：Oracle
- oracle臨時表空間的增刪改查Oracle
- Oracle表空間Oracle
- Oracle 表空間查詢相關sqlOracleSQL
- ORACLE 10g SYSAUX表空間快速增長之WRH$_ACTIVE_SESSION_HISTORY篇Oracle 10gUXSession
- oracle 10g SYSAUX表空間快速增長之WRI$_OPTSTAT_HISTGRM_HISTORY篇Oracle 10gUX
- Oracle查詢表空間的每日增長量Oracle
- Oracle - 表空間相關常用操作語句Oracle
- 關於oracle表空間的規劃方法Oracle
- oracle temp 表空間Oracle
- 增加oracle表空間Oracle
- oracle undo 表空間Oracle
- oracle users 表空間Oracle
- Oracle表空間管理Oracle
- oracle建立表空間Oracle
- Oracle 表空間管理Oracle
- oracle表空間操作Oracle
- ORACLE MOVE表空間Oracle
- ORACLE表空間概述Oracle
- Oracle表空間命令Oracle
- Oracle 表空間回收Oracle
- Oracle表移動表空間Oracle
- oracle 表移動表空間Oracle
- ORACLE AS 自動關閉Oracle
- oracle 10g SYSAUX表空間快速增長之STREAMS$_APPLY_SPILL_MESSAGES篇Oracle 10gUXAPP
- Oracle - 表空間使用情況及相關字典Oracle
- 淺述Oracle使用者表空間關係Oracle
- 華納雲：如何配置oracle表空間自動擴容？Oracle
- 【SQLServer】Tempdb空間異常增長，空間佔用非常大SQLServer
- 關於表空間和表的關係
- oracle表空間的整理Oracle
- Oracle 批量建表空間Oracle