moving .dbf files in Oracle 9i

[siridhar]

Hi, am asking this on behalf of my friend:

How to move the .dbf files from 1 drive to another in Oracle 9i? After the DB is imported it by deafult comes to C:, but she wants it to move to D:

Thanks for any replies.

 

[SantaMufasa]

Siridhar,

To move a file:

1) shutdown the database.
2) "mv" the .dbf file to the location you desire.
3) startup mount
4) ALTER DATABASE RENAME FILE '<old name>' TO '<new name>';
5) ALTER DATABASE OPEN;

If you would like, here is a script (which I call "RenameFiles.sql", to take care of steps 2 & 4 for you:

set echo off
set pagesize 0
set heading off
set feedback off
set verify off
set trimspool on
accept oldstr prompt "What string do you want to replace in filenames? "
accept newstr prompt "What should replace the old string in filenames? "
prompt
prompt
spool TempMV.sh
select 'mv '||member||' '||replace(member,ltrim(rtrim('&oldstr')),ltrim(rtrim('&newstr')))
	from v$logfile
	where instr(member,'&oldstr')>0;
select 'mv '||file_name||' '||replace(file_name,ltrim(rtrim('&oldstr')),ltrim(rtrim('&newstr')))
	from dba_data_files
	where instr(file_name,'&oldstr')>0;
spool off
prompt
prompt Wrote File "TempMV.sh"
prompt
spool TempRename.sql
select 'alter database Rename file' from dual;
select decode(rownum,1,'    ','   ,')||''''||
	file_name||'''' from dba_data_files
	where instr(file_name,'&oldstr')>0;
select '   ,'''||member||'''' from v$logfile
	where instr(member,'&oldstr')>0;
select 'to' from dual;
select decode(rownum,1,'    ','   ,')||''''||
	replace(file_name,ltrim(rtrim('&oldstr')),ltrim(rtrim('&newstr')))||''''
	from dba_data_files
	where instr(file_name,'&oldstr')>0;
select '   ,'''||
	''''||replace(member,ltrim(rtrim('&oldstr')),ltrim(rtrim('&newstr')))||''''
	from v$logfile
	where instr(member,'&oldstr')>0;
select ';' from dual;
spool off
prompt
prompt Wrote File "TempRename.sql"
prompt

@RenameFiles

What string do you want to replace in filenames? D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA
What should replace the old string in filenames? C:\DBDATA\DATA


mv D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA101.DBF C:\DBDATA\DATA101.DBF
mv D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA201.DBF C:\DBDATA\DATA201.DBF

Wrote File "TempMV.sh"

alter database Rename file
    'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA101.DBF'
   ,'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA201.DBF'
to
    'C:\DBDATA\DATA101.DBF'
   ,'C:\DBDATA\DATA201.DBF'
;

Wrote File "TempRename.sql"

You then run the two resulting scripts when you are ready, at the appropriate times. If you are running this on Windows and do not have an alias "mv" for the "move" command, just modify the resulting script's "mv" to "move".

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[siridhar]

Hey Mufasa, thank you for the reply.