因为原来的一个磁盘已经占据了 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
将所有能够自增长的表空间的自增长特性关闭,将新的数据文件全部写到新添加的/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