Script to Move Datafiles

Move datafile script (unix or windows)

ARCHIVE LOGGING (UNIX):

set linesize 150

column file_name format a60

set pagesize 200

set head off

select file_id, file_name

from dba_data_files

where file_name like '%disk8%'

/

select 'alter database datafile '||file_id||' offline;'

from dba_data_files

where file_name like '%disk8%'

/

select '!mv '||file_name||' '||file_name

from dba_data_files

where file_name like '%disk8%'

/

select 'alter database rename file "'||file_name||"' to "'||file_name||"';'

from dba_data_files

where file_name like '%disk8%'

/

select 'recover datafile '||file_id||';'

from dba_data_files

where file_name like '%disk8%'

/

select 'alter database datafile '||file_id||' online;'

from dba_data_files

where file_name like '%disk8%'

/

select 'select status from dba_data_files;' from dual;

NO ARCHIVE LOGGING (UNIX):

set linesize 150

column file_name format a60

set pagesize 200

set head off

select 'shutdown immediate' from dual;

select file_id, file_name

from dba_data_files

where file_name like '%disk8%'

/

select '!mv '||file_name||' '||file_name

from dba_data_files

where file_name like '%disk8%'

/

select 'startup mount' from dual;

select 'alter database rename file "'||file_name||"' to "'||file_name||"';'

from dba_data_files

where file_name like '%disk8%'

/

select 'alter database open;' from dual;

select 'select status from dba_data_files;' from dual;

ARCHIVE LOGGING (WINDOWS):

set linesize 150

column file_name format a60

set pagesize 200

set head off

select file_id, file_name

from dba_data_files

where file_name like '%disk8%'

/

select 'alter database datafile '||file_id||' offline;'

from dba_data_files

where file_name like '%disk8%'

/

select 'host move '||file_name||' '||file_name

from dba_data_files

where file_name like '%disk8%'

/

select 'alter database rename file "'||file_name||"' to "'||file_name||"';'

from dba_data_files

where file_name like '%disk8%'

/

select 'recover datafile '||file_id||';'

from dba_data_files

where file_name like '%disk8%'

/

select 'alter database datafile '||file_id||' online;'

from dba_data_files

where file_name like '%disk8%'

/

select 'select status from dba_data_files;' from dual;

NO ARCHIVE LOGGING (WINDOWS):

set linesize 150

column file_name format a60

set pagesize 200

set head off

select 'shutdown immediate' from dual;

select file_id, file_name

from dba_data_files

where file_name like '%disk8%'

/

select 'host move '||file_name||' '||file_name

from dba_data_files

where file_name like '%disk8%'

/

select 'startup mount' from dual;

select 'alter database rename file "'||file_name||"' to "'||file_name||"';'

from dba_data_files

where file_name like '%disk8%'

/

select 'alter database open;' from dual;

select 'select status from dba_data_files;' from dual;

Another Script

============

select 'alter database rename file '''|| file_name ||''' to '''/u0/' || 

substr(file_name, 5) ||''';' from dba_data_files;

to:

select 'alter database rename file '''|| file_name ||''' to '||'''/u0/' || 

substr(file_name, 5) ||''';' from dba_data_files;

Another
=======
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;

Another
=======
select 'alter database rename file '||chr(39)||name||chr(39)||' to
'||chr(39)||'F'||substr(name,2)||chr(39)||';'
from v$datafile;