How to rename and move a oracle system tablespace datafile?

advertisements

_____________________________________________________________________________________________________________________

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

0 comments:

Post a Comment

Labels

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)
 

acehints.com Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer