How to rename and move a oracle system tablespace datafile?

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’
DBA Tips Data Pump Reference


Post a Comment


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