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 被写爆的极端情况出现