How to rename and move a oracle system tablespace datafile?

advertisements

_____________________________________________________________________________________________________________________

System, temp, undo tablespaces cannot be make offline when the database is open. This can be done with following method.

Step1. Login to Oracle as sysdba

          Sqlplus “/ as sysdba”

Step2. Shutdown the database

          sql> shutdown immediate

Step3. Copy the targeted system 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/system_data01.dbf /data/oracle/prod1/prodsystem_data01.dbf

Step4. Mount the database.

          sql> startup mount

Step5. Rename the datafile using alter database command.

          alter database rename file ‘data/oracle/prod/system_data01.dbf’ to  ‘/data/oracle/prod1/prodsystem_data01.dbf’

_____________________________________________________________________________________________________________________

Website Stats