Script to move Oracle Datafiles

Here is a nice script if you need to move your datafiles to a new location.

SQL>spool rename_datafile.sql

set line 140
set pagesize 2000
set heading off
select ‘alter database rename file ‘ ||””|| file_name || ”” || ‘ to ‘|| ””||
‘/oracle/IMPRD/data’ || substr(file_name, instr(file_name, ‘/’, -1)) ||”” || ‘;’
from dba_data_files
/
SQL>spool off

Spool to a file and then:

1- Shutdown immediate;

2- OS copy the files to the new location

3- Startup Mount;

4- Execute the spooled file ( SQL>@rename_datafile.sql )

5- Alter database open;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s