How to move oracle datafile to different location and rename?

advertisements

_____________________________________________________________________________________________________________________

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’

          Or

          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.

_____________________________________________________________________________________________________________________

Website Stats