Undo table space is not enough. There are two ways to deal with it

1. Expand the table space size;

2. Create a new undo table space and delete the original one.

1、 Preliminary operation

Confirm undo table space name

select name from v$tablespace; 

Check the occupancy of undo table space and the storage location of data files;

select file_ name,bytes/1024/1024 from dba_ data_ files where tablespace_ name like ‘UNDOTBS1’; 

2、 Expand undo table space

alter   database  UNDOTBS1 datafile   ‘/opt/oracle/oradata/inms/undotbs02.dbf’   resize   4000M; 

3、 Create a new undo table space and delete the original one

1. Create a new undo table space and set the automatic extension parameters;

create undo tablespace undotbs2 datafile ‘/oradata/oradata/ddptest/UNDOTBS1.dbf’ size    2 1000m reuse autoextend on next 800m maxsize unlimited; 

2. Change the SPFILE configuration file dynamically;

alter system set undo_ tablespace=undotbs2 scope=both; 

3. Delete the original undo table space;

drop tablespace undotbs1 including contents; 

4. Confirm whether the deletion is successful;

select name from v$tablespace; 

5. Determine $Oracle_ HOME/dbs/ spfileoinms.ora Whether the content has changed:

$more spfileoinms.ora  

*.undo_ management=’AUTO’

*.undo_ retention=10800

*.undo_ tablespace=’UNDOTBS2′ 

If there is no change, execute the following statement:

SQL> create pfile from spfile; 

File created. 

6. Delete the data file of the original undo table space, and the file name is the result of the step.  

#rm $ORACLE_ BASE/oradata/$ORACLE_ SID/undotbs01.dbf

4、 Conclusion

According to the actual situation, expanding undo table space can only support for a period of time. After running for a period of time, the undo table space data file will eventually reach the upper limit. Therefore, the second scheme is adopted. You can use the script to replace the undotbs1 and undotbs2 table spaces alternately, which can quickly solve the problem.

（1） Script 1: replace the undotbs2 table space with undotbs1

create undo tablespace undotbs1 datafile ‘/u01/oracle/oradata/orcl/UNDOTBS1.dbf’ size 512m reuse autoextend on next 512m maxsize unlimited;

 

alter system set undo_ tablespace=undotbs1 scope=both;

 

drop tablespace undotbs2 including contents;

 

rm ‘/u01/oracle/oradata/orcl/UNDOTBS2.dbf’

（2） Script 2: replace the undotbs1 table space with undotbs2

create undo tablespace undotbs2 datafile ‘/u01/oracle/oradata/orcl/UNDOTBS2.dbf’ size 512m reuse autoextend on next 512m maxsize unlimited;

 

alter system set undo_ tablespace=undotbs2 scope=both;

 

drop tablespace undotbs1 including contents;

 

rm ‘/u01/oracle/oradata/orcl/UNDOTBS1.dbf’