If you want to move on 1 disk, here's a quick way.


Steps to follow:

1. Run the first section below (It will generate a file called "move_data_file.sql" to run later).

2. Shutdown the database.

3. Copy the datafiles to the new location at operating system level.

4. Mount the database.

5. Run the script move_data_file.sql (file generated at section 1)

6. Open the database.


---------------------------------------------------------------------------------------
FIRST SECTION (YOU CAN SAVE THIS IN A .SQL FILE EX: rename_datafile.sql)
---------------------------------------------------------------------------------------

define data_new_dir = &&data_new_dir

set feedback off
set termout off
set verify off
set linesize 120
set pagesize 60
set heading off

spool c:\move_data_file.sql
select 'alter database rename file ''' || name || ''' to ''' || '&data_new_dir' || substr(name,decode(instr(name,'\',-1),0,instr(name,'/',-1),instr(name,'\',-1))+ 1) || ''';'
from v$datafile;
spool off;

set feedback on
set termout on

Prompt
Prompt This are the modification that will be done
Prompt Press [ENTER] to continue...

pause;

select 'alter database rename file ''' || name || ''' to ''' || '&data_new_dir' || substr(name,decode(instr(name,'\',-1),0,instr(name,'/',-1),instr(name,'\',-1))+ 1) || ''';'
from v$datafile;

set verify on
set heading on

Prompt End.
pause;

---------------------------------------------------------------------------------------
SAMPLE OUTPUT OF STEP 1:
---------------------------------------------------------------------------------------

SQL> @c:\rename_datafile.sql
Enter value for data_new_dir: E:\DATABASE\

This are the modification that will be done
Press [ENTER] to continue...


alter database rename file 'D:\ORA8I\ORANT\ORADATA\ORCL\SYSTEM01.DBF' to 'E:\DATABASE\SYSTEM01.DBF';
alter database rename file 'D:\ORA8I\ORANT\ORADATA\ORCL\RBS01.DBF' to 'E:\DATABASE\RBS01.DBF';
alter database rename file 'D:\ORA8I\ORANT\ORADATA\ORCL\USERS01.DBF' to 'E:\DATABASE\USERS01.DBF';
alter database rename file 'D:\ORA8I\ORANT\DATABASE\TEMPO.DBF' to 'E:\DATABASE\TEMPO.DBF';

End.

---------------------------------------------------------------------------------------