RENAMING A DATAFILE ORACLE IN 10G and 11G.

RENAMING A DATAFILE ORACLE IN 10G and 11G.
=============================================

1. To rename a datafile in Oracle , shutdown database

Sql>SHUT IMMEDIATE;

2. copy/move datafile from old location to new location


cp /u01/app/oracle/oradata/imrdev/system01.dbf  /u05/oradata/imrdev/data01/system01.dbf
cp /u01/app/oracle/oradata/imrdev/undotbs01.dbf  /u05/oradata/imrdev/data01/undotbs01.dbf
cp /u01/app/oracle/oradata/imrdev/sysaux01.dbf   /u05/oradata/imrdev/data01/sysaux01.dbf
cp /u01/app/oracle/oradata/imrdev/users01.dbf   /u05/oradata/imrdev/data01/users01.dbf

3. startup database in mount mode. Run below command


sql>alter database datafile rename '/u01/app/oracle/oradata/imrdev/system01.dbf' to '/u05/oradata/imrdev/data01/system01.dbf';
sql>alter database rename file '/u01/app/oracle/oradata/imrdev/undotbs01.dbf' to '/u05/oradata/imrdev/data01/undotbs01.dbf';
sql>alter database rename file '/u01/app/oracle/oradata/imrdev/sysaux01.dbf' to  '/u05/oradata/imrdev/data01/sysaux01.dbf';
sql>alter database rename file '/u01/app/oracle/oradata/imrdev/users01.dbf' to  '/u05/oradata/imrdev/data01/users01.dbf' ;

4.open database in read/write mode.

Sql>alter database open;

5. Check new location of file by running.

sql>select name from V$datafile;

6. Now you can delete datafile from old location.


rm /u01/app/oracle/oradata/imrdev/system01.dbf
rm /u01/app/oracle/oradata/imrdev/undotbs01.dbf
rm /u01/app/oracle/oradata/imrdev/sysaux01.dbf
rm /u01/app/oracle/oradata/imrdev/users01.dbf