How to Rename or Move Oracle Tablespace Datafile to Another Location

Oracle database does not provide an easy user interface to rename a datafile of tablespace, nor database administrator can easily move or relocate the datafile to another location or directory that different from original location on creation of database. The rename or move place task has to be performed via Oracle SQLPlus command line interface. However, if the operation is performed when the tablespace which owns the datefile is online, error will occur.

The error message may include the following:

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 the datafile around or rename the datafile, follow this guide:

1. Login to SQLPlus.
2. Connect as SYS DBA with CONNECT / AS SYSDBA command.
3. Shutdown the database instance with SHUTDOWN command.
4. Rename or/and move the datafiles at operating system level.
5. Start Oracle database in mount state with STARTUP MOUNT command.
6. Modify the name or location of datafiles 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 datafiles that need to be changed or moved do not belong to SYSTEM tablespaces, and do not contain active rollback segments or temporary segments, there is another workaround that does not require database instance to be shutdown. Instead, only the particular tablespace that contains the date files is taken offline.

1. Login to SQLPlus.
2. Connect as SYS DBA with CONNECT / AS SYSDBA command.
3. Make offline the affected tablespace with ALTER TABLESPACE <tablespace name> OFFLINE; command.
4. Modify the name or location of datafiles in Oracle data dictionary using following command syntax:

   ALTER TABLESPACE <tablespace name> RENAME DATAFILE ‘<fully qualified path to original data file name>’ TO ‘<new or original fully qualified path to new or original data file name>’;

5. Bring the tablespace online again with ALTER TABLESPACE alter tablespace <tablespace name> ONLINE; command.

Related Articles

• How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile
• How to Remove and Drop Datafiles from Tablespace in Oracle Database
• Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error
• ORA-25153 Temporary Tablespace is Empty Error in Oracle
• How to Move WordPress Blog to New Domain or Location
• Fix Vista Unable to Rename or Move Files and Folders Issues After Applying KB938979 Patch
• Change Oracle Database User Password
• Oracle Database Link
• Download Bulk Rename Utility To Rename Files Easily
• Oracle Database Import Error 3113/3114

This entry was posted on Saturday, August 4th, 2007 at 11:29 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.