Oracle Move Datafile



  1. Oracle Move Datafile Online
  2. Oracle Move Datafile Online

Rename the data files for Oracle

  1. Take the datafile offline with the 'ALTER DATABASE DATAFILE '/old/location' OFFLINE;' command. Copy or move the datafile to its new location. On Unix this can be done with the 'dd' command.
  2. There are multiple ways to move or rename the data files in oracle 11g, oracle 12c has come with a wonderful feature where we can move the files being online. However prior to 12c we need to do this practice after making the database offline, there are online ways are also available but in those also there is some time delay in processing.
  3. RMAN list copy of datafile 1; using target database control file instead of recovery catalog List of Datafile Copies Key File S Completion Time Ckp SCN Ckp Time - - - - 4 1 A 14-75 14-01-27 Name: C: ORACLEXE APP ORACLE ORADATA XE SYSTEM.DBF RMAN delete datafilecopy 4; allocated channel: ORADISK1.

How to rename or move a datafile in oracle. 14389 views 1 min, 32 sec read 3 If you are in 12c version,then renaming a datafile can be done online, without making.

Datafile movement can be performed without a database restart for those tablespaces, which can be taken offline (All except System, Undo or Temp).

Following are the steps for move or rename the Oracle Datafile:

1: Login to the database as SYSDBA

sqlplus sys as sysdba

2: Make the tablespace offline which file need to rename:

select tablespace_name,file_name from dba_data_files;

Oracle Move Datafile

Alter tablespace USERS offline;

3: Copy the datafile to the required location at the OS level

$ cp filename1 filename2

4: Rename the datafile using Alter Database command

alter database rename file ' to ' ;

Step 5: Make the tablespace online.

SQL> alter tablespace USERS online;

The above mentioned steps is not worked for system , temp and undo tablespaces as they cannot be taken offline.
Normally we drop and recreate the undo/temp tablespaces rather than moving the datafiles as it doesn’t involve any downtime.

Following are the steps to move system datafiles:

Oracle Move Datafile

Steps to move SYSTEM datafile from one location to another:

1: Login to the database as SYSDBA

sqlplus sys as sysdba

Step 2: Shutdown the database

Shutdown immediate

Step 3: Copy/rename the datafile at the OS level

$ cp filename1 filename2

Step 4: Start the database at MOUNT state.
At this stage control file is read.

Startup mount

Oracle Move Datafile Online

Step 5: Rename the datafile using Alter Database command

alter database rename file ' to ' ;

Oracle Move Datafile

Step 6: Open the database

Alter database open;

Oracle Move Datafile Online

For drop or recreate temp tablespace follows following link:
Manage the Temp Tablespace