Renaming Datafiles in Oracle

Sometimes I need to move datafiles physically to a new disk device or another filsystem. As I'm sure you are aware of, the controlfile needs to be updated. I have a small script that is quite handy when moving several files, or all the files in the database. This I got from a very handsome college of mine ;). Thx Magnus!

Script:

select 'alter database rename file '''||file_name||''' TO '''|| REPLACE(file_name,'C:\ORADATA\MYDB','E:\ORADATA\MYDB')||''';' from dba_data_files
union
select 'alter database rename file '''||member||''' TO '''||
REPLACE(member,'C:\ORADATA\MYDB','E:\ORADATA\MYDB')||''';' from v$logfile;


The output generated is all you need to move all your files from the C drive to the E drive:

alter database rename file 'C:\ORADATA\MYDB\DATA2OHIST001.DBF' TO 'E:\ORADATA\MYDB\DATA2OHIST001.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATA2OHIST002.DBF' TO 'E:\ORADATA\MYDB\DATA2OHIST002.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATA2OHIST003.DBF' TO 'E:\ORADATA\MYDB\DATA2OHIST003.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATADEF001.DBF' TO 'E:\ORADATA\MYDB\DATADEF001.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATAOHIST001.DBF' TO 'E:\ORADATA\MYDB\DATAOHIST001.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATAOHIST002.DBF' TO 'E:\ORADATA\MYDB\DATAOHIST002.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATAOHIST003.DBF' TO 'E:\ORADATA\MYDB\DATAOHIST003.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATASMALL001.DBF' TO 'E:\ORADATA\MYDB\DATASMALL001.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATASTD001.DBF' TO 'E:\ORADATA\MYDB\DATASTD001.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATASTD002.DBF' TO 'E:\ORADATA\MYDB\DATASTD002.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATATRANS001.DBF' TO 'E:\ORADATA\MYDB\DATATRANS001.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATATRANS002.DBF' TO 'E:\ORADATA\MYDB\DATATRANS002.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATATRANS003.DBF' TO 'E:\ORADATA\MYDB\DATATRANS003.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATATRANS004.DBF' TO 'E:\ORADATA\MYDB\DATATRANS004.DBF';
...