1、查看使用哪个undo

select name,value from v$parameter where name='undo_tablespace';

undo 表空间的大小

select sum(bytes/1024/1024/1024) "undo sizeG" from dba_data_files where TABLESPACE_NAME='UNDOTBS2';

CREATE UNDO TABLESPACE TMP_UNDO1 DATAFILE '/oradata/UD02.DBF' SIZE 2048M AUTOEXTEND OFF;

2.把现在的undo表空间指向这个新建的表空间

ALTER SYSTEM SET UNDO_TABLESPACE=TMP_UNDO1;

commit;

3.把原来的表空间文件删除

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

4.在原来undo表空间所在地新建一个undo表空间

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/oradata/undotbs01.dbf' SIZE 1024M AUTOEXTEND ON next 50m maxsize 30720m;

5.把系统undo表空间指向第4步中建好的undo表空间

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1;

6.把第1步建立的临时undo表空间删除

DROP TABLESPACE TMP_UNDO1

INCLUDING CONTENTS AND DATAFILES;

ITPub博客

更改undo表空间大小