Oracle database does not have an easy-to-use interface and this is a major problem when you want to rename / move a data file of tablespace. The rename or move place task has to be performed via the Oracle SQLPlus command line interface and the database administrator has to be very careful in executing the commands. For instance, if the operation is performed while the tablespace that owns the data file is online, an error will occur, and an error message will be displayed that may look something like:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file <string> – file is in use or recovery
ORA-01110: data file <string> : ‘datafile.dbf’
To properly move or rename the data file, follow the steps listed below:
1> Login to SQLPlus.
2> Connect as SYS DBA with CONNECT / AS SYSDBA command.
3> Shutdown the database instance with SHUTDOWN command.
4> Rename and/or move the data files at operating system level.
5> Start Oracle database in mount state with STARTUP MOUNT command.
6> Modify the name or location of data files in Oracle data dictionary using following command syntax:
ALTER DATABASE RENAME FILE ‘<fully qualified path to original data file name’ TO ‘<new or original fully qualified path to new or original data file name>’;
7> Open Oracle database instance completely with ALTER DATABASE OPEN command.
If the data files don’t belong to SYSTEM tablespaces, and don’t contain active rollback or temporary segments, then you don’t have to necessarily shutdown the database instance. Instead, you can simply take the particular tablespace offline.
1> Login to SQLPlus.
2> Connect as SYS DBA with CONNECT / AS SYSDBA command.
3> Make the concerned tablespace offline with ALTER TABLESPACE
OFFLINE command.
4> Modify the name or location of data files in Oracle data dictionary using following command syntax:
ALTER TABLESPACE
RENAME DATAFILE ‘<correct path to original data file name>’ TO ‘<correct path to new data file name>’.’
5> Bring the tablespace online again with ALTER TABLESPACE alter tablespace
ONLINE command.