The Geek Diary

Oracle Database – How to Rename a Datafile with Special Characters Created by Mistake

by

Question: We have mistakenly create a datafile with special charatcers. Also we get error ORA-01516 when we try to rename it using the normal method:

sql> alter database rename file 'old_file_name' to 'new_file_name';

The Solution

1. Bring the Tablespace offline.

sql> alter tablespace [tablespace name] offline;

2. Copy/Move the File at the OS level specifying the correct file name.

3. If there is no special character then the solution is:

sql> alter database rename file [old_file_name] to [new_file_name];

4. If there is special character then, we need to find out the datafile name by giving the datafile number and rename the datafile. We can use the below procedure:

DECLARE
str varchar2(50);     <<======== Size needs to be adjusted based on the length of datafile name and path.
BEGIN
Select name into str from v$datafile where file# = 33;
execute immediate 'alter database rename file ''' || str || ''' to ''''';
END;

5. Bring back the Tablespace online:

sql> alter tablespace [tablespace name] online;

For 12c

For 12c databases, there is an easier solution for this using the new 12c “move datafile” command.

sql> alter tablespace INDX_DATA$OLD rename to INDX_DATA_OLD;
sql> alter database move datafile '+DATADG/XPCLRC/DATAFILE/indx_data$old.1168.888920899' to '+DATADG';

You May Also Like