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 %
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
You can simply create a new undo Ts and drop the old one .
show parameter undo
NAME TYPE VALUE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE '+DATA_NEWPATH' SIZE 100M AUTOEXTEND ON;
SQL> alter system set undo_tablespace=undotbs2;
After that can drop UNDOTBS1. Take a look here for more information.