Oracle: Change datafile names

Follow below procedure to change data file names, you may need make changes to fit your case.

Procedure:

    Create respect scripts to change data file names at system and database level
    Shutdown Database
    Run script to change data file names at system level
    Run script to change data file names at database level
    Start up database

Practice:

1. Using below statement to generate script to change datafile names in database

SQL> select 'alter database rename file ''' || file_name || '''' || ' to ' || '''' || SUBSTR(file_name, 1,19) || 'pkg' || SUBSTR(file_name, 24) || ''';' from dba_data_files where file_name like '%gw%' order by file_name;

and export the result into renDBfiles.sql

2. Using below statement to generate shell script to rename data files at system level

SQL> select 'mv ' || file_name || ' ' || SUBSTR(file_name, 1,19) || 'pkg' || SUBSTR(file_name, 24) from dba_data_files where file_name like '%gw%' order by file_name;


and export the result into renSysfiles.sh

3. Shutdown Database
SQL> shutdown immediate;

4. Rename data files at system level
$ ./renSysfiles.sh

5. Rename the data files at mount stage (database is not open)
SQL> startup mount;

6. Rename date files at database level
SQL> @renDBfiles.sql

7. restart database
SQL> SHUTDOWN IMMEDIATE
SQL> startup