URGENT - Relocating Datafiles
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: URGENT - Relocating Datafiles

  1. #1
    Join Date
    Dec 2002
    Location
    Herts, UK
    Posts
    10

    URGENT - Relocating Datafiles

    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
    Indy

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Got just what you want in this link...
    http://technet.oracle.com/docs/produ...dfiles.htm#636

    HTH.

  3. #3
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    This document explains the process in full

    http://support.oracle.co.uk/metalink...&p_id=115424.1

    HTH
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  4. #4
    Join Date
    Nov 2000
    Posts
    440
    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.

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

  5. #5
    Join Date
    Dec 2002
    Location
    Herts, UK
    Posts
    10
    Thankyou all for helping me out so quickly - much appreciated.
    Indy

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width