Oracle – How to Rebalance, Move, or Shrink Undotablespace

oracletablespaces

I'm running out of space in oradata01 and oradata02. Is there a ways to move or rebalance with oradata03?

/opt/oracle/oradata01/x/undotbs01.dbf   4   100 %   
/opt/oracle/oradata02/x/undotbs02.dbf   17  100 %   
/opt/oracle/oradata03/x/undotbs03.dbf   25  1 %

Best Answer

If you are on 12c then it's easy - just online move datafile.

SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata02/x/undotbs02.dbf' TO '/opt/oracle/oradata03/x/undotbs02.dbf';

If you are on lower version, and if you can afford stopping instance, then

1) shutdown database

2) move datafile to oradata03 by os command

3) mount database

4) rename datafile 

SQL> ALTER DATABASE rename file '/opt/oracle/oradata02/x/undotbs02.dbf' TO '/opt/oracle/oradata03/x/undotbs02.dbf';

If you can't stop the instance, then you can create another undo tablespace and switch to it online. https://docs.oracle.com/cd/B28359_01/server.111/b28310/undo005.htm#ADMIN11470

You can also try to resize datafiles, it will work, if there are no active/retention undo segments at the end of datafiles.

Related Question