How to rename and move a oracle system tablespace datafile?



The Eucharistic Miracles of the World
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


Post a Comment


Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2) Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer