Last year, I wrote a tip, Controlfiles...Make Them Part of Your Data File Relocation Program, about moving / renaming data files. That tip was also published in our ebook, Oracle Tips from the Real World…52 Weeks with Alydan Consulting. In that tip was a one sentence reference to moving data files with the database open that, while not really the point of the tip, needs further explanation. That sentence has been changed in the ebook, and this tip provides the actual steps necessary to move a data file with the database open. 

Please note: This procedure does not apply to the SYSTEM, TEMP, and UNDO tablespaces!

To move / rename a data file, the steps are:

• Take the tablespace offline.

• Using operating system commands, copy the data file using the new file name / location.

• Use the ALTER TABLESPACE command with the RENAME DATAFILE option to change the file name / location in the database.

• Bring the tablespace back online.

In this example, I will create a tablespace test with a data file named test01. Having forgotten to include the extension in the data file name and put the file in the wrong directory, I will then add the extension and put the data file in the correct location.

First, to create the tablespace:

CREATE TABLESPACE test
DATAFILE 'C:\ORACLE\ORADATA\ALYDAN\test01' SIZE 10M;

Tablespace created.

Darn! I really meant to put the data file in the dbf subdirectory and I forgot the dbf extension. So, let's take the tablespace offline.

ALTER TABLESPACE test OFFLINE;

Tablespace altered.

Now, through the operating system, copy the data file to the correct directory with the correct name.

C:\oracle\oradata\alydan>copy test01 dbf\test01.dbf
        1 file(s) copied.

Finally, back into SQL*Plus to change the file name and location in the database.

ALTER TABLESPACE test
RENAME DATAFILE 'C:\ORACLE\ORADATA\ALYDAN\test01'
TO 'C:\ORACLE\ORADATA\ALYDAN\DBF\test01.dbf';

Tablespace altered.

ALTER TABLESPACE test ONLINE;

Tablespace altered.

Data file successfully moved and renamed. I can now delete the original data file, as it is no longer needed.

Note: This tip was tested using Oracle9ir2.

Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click here for the tip feedback page. Thank you.