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';