The Oracle (tm) Users' Co-Operative FAQ

How do I rename a data file ?


Author's name: Stephen Bell

Author's Email: stephen.bell@sympatico.ca

Date written: August 11, 2001

Addendum: 10th Jan 2002

Oracle version(s): 8.1.7

The methodology for renaming an existing datafile can also be used to move it. To change the location of a datafile in Oracle one uses the same "rename" command, but specifies a new path and/or filename. Whether moving the datafile, or simply renamingit at its current location, the Oracle commands serve essentially to rename pointers to the file ; operating system commands must be usedto ensure a file of the name specified exists at the specified location.

Back to index of questions


Datafiles can be moved or renamed using one of two methods: alter database or alter tablespace.

The main difference between them is that alter tablespace only applies to datafiles that do not contain the SYSTEM tablespace, active rollback segments, or temporary segments, but the procedure can be performed while the instance is still running. The alter database method works for any datafile, but the instance must be shut down.

The alter database method:

1. Shut down the instance.

2. Rename and/or move the datafile using operating system commands.

3. Mount the database and use alter database to rename the file within the database. A fully qualified filename is required in the syntax of your operating system. For example to rename a file called 'data01.dbf ' to ' data04.dbf ' and move it to a new location at the same time (at this point in the example the instance has been shutdown) and;

4. Start the instance.

	> svrmgrl
	  SVRMGR>  connect sys/oracle as sysdba;
	  SVRMGR>  startup mount U1;
	  SVRMGR>  alter database rename file '/u01/oracle/U1/data01.dbf ' TO '/u02/oracle/U1/data04.dbf ' ;
	  SVRMGR> alter database open;

Notice the single quotes around the fully qualified filenames and remember, the files must exist at the source and destination paths. The instance is now open using the new location and name for the datafile.

The alter tablespace method:

This method has the advantage that it doesn't require shutting down the instance, but it only works with non-SYSTEM tablespaces. Further, it can't be used for tablespaces that contain active rollback segments or temporary segments.

1. Take the tablespace offline.

2. Rename and/or move the datafile using operating system commands.

3. Use the alter tablespace command to rename the file in the database.

4. Bring the tablespace back online.

	SVRMGR> connect sys/oracle as sysdba
	SVRMGR> alter tablespace app_data offline;
	SVRMGR> alter tablespace app_date rename datafile '/u01/oracle/U1/data01.dbf ' TO '/u02/oracle/U1/data04.dbf ' ;
	SVRMGR> alter tablespace app_data online;

The tablespace will be back online using the new name and/or location of the datafile.

Both of these methodologies can be used within Oracle Enterprise Manager also.


Further reading N/A

This question is also addressed by the following documents:

Author Title/URL Suggested by Referee's comments
Howard Rogers How do I rename or move a datafile ? Jonathan Lewis A short pdf file with an intelligent discussion of the issues. You will need Adobe Acrobat to read this document.

NOTE - The referenced site was closed down by Oracle Australia. in May 2002. This link has been maintained in case Howards gets permission to re-open the site.

       

.


Back to top

Back to index of questions