We need to urgently relocate datafiles to a different partition on our NT server. Could somebody please give me the procedure along with the appropriate SQL for each stage.
Thankyou
Printable View
We need to urgently relocate datafiles to a different partition on our NT server. Could somebody please give me the procedure along with the appropriate SQL for each stage.
Thankyou
Got just what you want in this link...
http://technet.oracle.com/docs/produ...dfiles.htm#636
HTH.
This document explains the process in full
http://support.oracle.co.uk/metalink...&p_id=115424.1
HTH
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.
---------------------------------------------------------------------------------------
Thankyou all for helping me out so quickly - much appreciated.
Your next urgent task should be to learn how to use Oracle documentation, free, online at http://tahiti.oracle.com. How urgent was your problem? What if no one here answered your question for seven hours? Virtually all Oracle DBA books, third party or otherwise, show you step by step how to do that task. You can also find the procedures at metalink.oracle.com if you have a CSI.