How to move oracle datafile to different location and rename?

These are the steps to move an oracle datafile to different location. This can be applicable for the tablespaces other than system, undo, temp.

Step1. Login to Oracle as sysdba
          Sqlplus “/ as sysdba”
Step2. Bring down the tablespace which belongs to the targeted datafile to offline
          Alter tablespace <tablespace_name> offline
          Alter tablespace ts1 offline;
Step3. Copy the targeted datafile to different location using OS commands. If you wanted to rename the datafile to different name, copy the file with targeted new name.
          cp <path/filename> <NewPath/newFilename>
   eg.cp /data/oracle/prod/ts1_data01.dbf /data/oracle/prod1/prodts1_data01.dbf
Step4. Rename the datafile using alter database command.
          Alter database rename file ‘/data/oracle/prod/ts1_data01.dbf’ to  ‘/data/oracle/prod1/prodts1_data01.dbf’
          You can use alter tablespace command to rename the datafile
          Alter tablespace ts1 rename datafile file ‘/data/oracle/prod/ts1_data01.dbf’ to  ‘/data/oracle/prod1/prodts1_data01.dbf’

Step5. Switch the tablespace to online.
          Alter tablespace <tablespace_name> online;
          Alter tablespace ts1 online;
System, temp, undo tablespaces datafiles cannot be renamed or moved in above method as they cannot make it offline. This can be done with following method.
DBA Tips Data Pump Reference


Post a Comment


dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google