Rename a database file
At times it may be necessary to move or rename datafiles belonging to a database for performance reasons or to have a meaningful name. There are two methods of doing this. The first is at a database level, which needs the database to be in the mount state and the second is at a tablespace level. I would choose the first method if there are many files to be reorganized - prepare a script and run it with the database in the mount state (not opened).
Database Level - Steps:
1. Shutdown normal the database.
2. Rename or copy the files to their new name or location.
3. Startup and mount the database
4. Rename the datafile inside Oracle.
5. Open the database
A redo log file also can be renamed in the above method.
Tablespace Level - Steps:
If the database is 24x7 and cannot be shutdown, files can be reorganized by taking the tablespace offline.
1. Make the tablespace where the datafile belongs to offline.
2. Copy the files at the operating system.
3. Rename the datafile inside Oracle.
4. Make the tablespace online.
Once the datafiles are renamed or moved, it is better to create a backup of the control file.
The control file creation script is written to the USER_DUMP_DEST (init.ora parameter) directory.
You cannot drop or delete a datafile.