We have a Virtual-DBA client that was rapidly running out of space for one of their databases in VMWare. To rectify the problem we decided to move a few tablespaces to a new LUN/disk presented to the VMWare OS.
We had a scheduled downtime window so we didn’t need to leave the database online to perform the move. If we had wanted to leave the database online we could have used RMAN to copy the files ahead of time, took the tablespace offline and then just recovered the tablespace.
Instead we took the database offline and then made a copy of the database files from one drive to the new drive. We then started the database in mount mode. After the database was up we used the below command in a sqlplus prompt to change the file location.
alter database rename file ‘/u01/oradata/datafile1.dbf’ to ‘/u02/oradata/datafile1.dbf’;
We used the above command to change all the datafiles associated with the tablespaces we wanted to move and then we simply opened the database with alter database open command.
We then verified that the datafiles were moved by selecting from the v$datafile view.
Once everything was verified we removed/deleted the old datafiles and then took a full backup of the database.