Move datafile script (unix or windows)
Posted by philiphowe on June 11, 2010
Remember to change the disk name.
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;
