Move An Oracle Datafile To A New Location

Had a filesystem fill up on an AIX server today and there was nothing of significant size that I could delete. I could have asked the AIX admin to add some more space, but since it was just a training environment and there were other filesystems with available space, I chose to move some of the datafiles. It's pretty easy to do. Here are the steps.

1. Shutdown the database
2. Physically move (or copy if you want to be extra safe) the datafiles to their new location.
3. Mount the database (startup mount)
4. Rename the datafiles in Oracle with the following command:
alter database rename file 'old full path and file name' to 'new full path and file name';
ex. alter database rename file '/u81/oradata/train/ilawtrain_01.dbf' to '/u91/oradata/train/ilawtrain_01.dbf';

5. Open the database (alter database open)
6. Verify that the datafiles are in the new location in dba_data_files
7. If the datafiles are in the new location as expected and you copied the files instead of moving them, you can now safely delete the old copies of the datafile. Again if you want to be safe, rename the old copies and wait a day or two to delete them.