Oracle 11g 扩展UNDO表空间
1.查看数据文件位置
set linesize 200
col file_name for a50
col tablespace_name for a20
select file_id,file_name,tablespace_name,sum(bytes)/1024/1024 total_mb,autoextensible from dba_data_files group by file_name,file_id,tablespace_name,autoextensible order by file_id;
SYS@PROD1 > /
FILE_ID FILE_NAME TABLESPACE_NAME TOTAL_MB AUTOEX
---------- -------------------------------------------------- -------------------- ---------- ------
1 /u01/app/oracle/oradata/PROD/system01.dbf SYSTEM 970 YES
2 /u01/app/oracle/oradata/PROD/sysaux01.dbf SYSAUX 570 YES
3 /u01/app/oracle/oradata/PROD/undotbs01.dbf UNDOTBS1 30 YES
4 /u01/app/oracle/oradata/PROD/users01.dbf USERS 65 YES
5 /u01/app/oracle/oradata/PROD/test.dbf TEST 100 NO
2.如果UNDO表空间是自动扩展,在确定没有业务进行的情况下,关掉自动扩展
SYS@PROD1 > alter database datafile '/u01/app/oracle/oradata/PROD/undotbs01.dbf' autoextend off;
Database altered.
SYS@PROD1 > set linesize 200
SYS@PROD1 > col file_name for a50
SYS@PROD1 > col tablespace_name for a20
SYS@PROD1 > select file_id,file_name,tablespace_name,sum(bytes)/1024/1024 total_mb,autoextensible from dba_data_files group by file_name,file_id,tablespace_name,autoextensible order by file_id;
FILE_ID FILE_NAME TABLESPACE_NAME TOTAL_MB AUTOEX
---------- -------------------------------------------------- -------------------- ---------- ------
1 /u01/app/oracle/oradata/PROD/system01.dbf SYSTEM 970 YES
2 /u01/app/oracle/oradata/PROD/sysaux01.dbf SYSAUX 570 YES
3 /u01/app/oracle/oradata/PROD/undotbs01.dbf UNDOTBS1 30 NO
4 /u01/app/oracle/oradata/PROD/users01.dbf USERS 65 YES
5 /u01/app/oracle/oradata/PROD/test.dbf TEST 100 NO
3.添加数据文件,扩展UNDO表空间
alter tablespace UNDOTBS1 add datafile '/u01/app/oracle/oradata/PROD/undotbs02.dbf' size 120M;
SYS@PROD1 > alter tablespace UNDOTBS1 add datafile '/u01/app/oracle/oradata/PROD/undotbs02.dbf' size 120M;
Tablespace altered.
查询数据文件,显示已添加成功
SYS@PROD1 > select file_id,file_name,tablespace_name,sum(bytes)/1024/1024 total_mb,autoextensible from dba_data_files group by file_name,file_id,tablespace_name,autoextensible order by file_id;
FILE_ID FILE_NAME TABLESPACE_NAME TOTAL_MB AUTOEX
---------- -------------------------------------------------- -------------------- ---------- ------
1 /u01/app/oracle/oradata/PROD/system01.dbf SYSTEM 970 YES
2 /u01/app/oracle/oradata/PROD/sysaux01.dbf SYSAUX 570 YES
3 /u01/app/oracle/oradata/PROD/undotbs01.dbf UNDOTBS1 30 NO
4 /u01/app/oracle/oradata/PROD/users01.dbf USERS 65 YES
5 /u01/app/oracle/oradata/PROD/test.dbf TEST 100 NO
6 /u01/app/oracle/oradata/PROD/undotbs02.dbf UNDOTBS1 120 NO
查询表空间大小,UNDO表空间大小已改变
select tablespace_name,autoextensible,sum(bytes)/1024/1024 total_mb from dba_data_files group by tablespace_name,autoextensible;
SYS@PROD1 > select tablespace_name,autoextensible,sum(bytes)/1024/1024 total_mb from dba_data_files group by tablespace_name,autoextensible;
TABLESPACE_NAME AUTOEX TOTAL_MB
-------------------- ------ ----------
TEST NO 100
USERS YES 65
SYSAUX YES 570
SYSTEM YES 970
UNDOTBS1 NO 150