How to easily rename all Oracle database datafiles (UNIX/Linux)

The following script helps to rename the Oracle database datafiles to a different location. It will generate executable output as 'alter database rename file..'. You just need to specify the path (without the trailing '/'), where you want to rename the datafiles.

set serveroutput on;
set linesize 300

declare
  cursor df is
    select name from v$datafile
    union all
    select name from v$tempfile
    union all
    select member as name from v$logfile;
   
  cursor df2 is
    select case when instr (file_name, '/') > 0
         then
         substr (file_name, instr (file_name, '/', -1) + 1)
         end as file_name
    from (select name as file_name from v$datafile
    union all
    select name as file_name from v$tempfile
    union all
    select member as file_name from v$logfile);

  newPath varchar2(300);
  s_df varchar2(300);
  s_df2 varchar2(300);
 
begin
--  dbms_output.put_line('Enter the renamed database path (omit trailing ''/''):');
  newPath := '&PATH';

  open df;
  open df2;

  fetch df into s_df;
  fetch df2 into s_df2;

  while df%FOUND loop
    dbms_output.put_line('alter database rename file '''||s_df||''' to '''||newPath||'/'||s_df2||''';');
    fetch df into s_df;
    fetch df2 into s_df2;
  end loop;

  close df;
  close df2;
end;
/

You get for example the following output.

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 9 14:19:51 2010

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>@plsql_alter_database_rename_file.sql
Enter value for path: /path/to/the/new/localtion
old  26:   newPath := '&PATH';
new  26:   newPath := '/path/to/the/new/localtion';
alter database rename file '/current/datafile/path/system01.dbf' to '/path/to/the/new/localtion/system01.dbf';
alter database rename file '/current/datafile/path/undotbs01.dbf' to '/path/to/the/new/localtion/undotbs01.dbf';
alter database rename file '/current/datafile/path/sysaux01.dbf' to '/path/to/the/new/localtion/sysaux01.dbf';
alter database rename file '/current/datafile/path/users01.dbf' to '/path/to/the/new/localtion/users01.dbf';
alter database rename file '/current/datafile/path/temp_small01.dbf' to '/path/to/the/new/localtion/temp_small01.dbf';
alter database rename file '/current/datafile/path/redo14_2.rdo' to '/path/to/the/new/localtion/redo14_2.rdo';
alter database rename file '/current/datafile/path/redo15_2.rdo' to '/path/to/the/new/localtion/redo15_2.rdo';
alter database rename file '/current/datafile/path/redo16_2.rdo' to '/path/to/the/new/localtion/redo16_2.rdo';
alter database rename file '/current/datafile/path/redo13_1.rdo' to '/path/to/the/new/localtion/redo13_1.rdo';
alter database rename file '/current/datafile/path/redo14_1.rdo' to '/path/to/the/new/localtion/redo14_1.rdo';
alter database rename file '/current/datafile/path/redo15_1.rdo' to '/path/to/the/new/localtion/redo15_1.rdo';
alter database rename file '/current/datafile/path/redo16_1.rdo' to '/path/to/the/new/localtion/redo16_1.rdo';

PL/SQL procedure successfully completed.