Tuesday, September 16, 2008

Rename or Move Data file

Yesterday one of the DBA did a mistake and now we want to move the data file to other location.

We have There is two option for rename datafiles.
Option:1

1. shutdown the database


2. Rename or move datafiles at file system

3. startup mount the database

4. rename datafiles in the database
SQL> alter database rename file '/db/u01/oradata/imerge1/dbuser_01.dbf' to '/db/u03/oradata/imerge1/dbuser_01.dbf';

5. Open the database

Option: 2

1. Make the tablespace offline to rename or move datafiles
SQL> alter tablespace dbuser offline;

2. Rename or move datafiles at file system

3. Now we need to rename or move the datafiles in the database
SQL> alter tablespace TBS_NAME rename datafile
'/db/u01/oradata/imerge1/dbuser_01.dbf' to '/db/u03/oradata/imerge1/dbuser_01.dbf';

4. Online the tablespace which was made offline.
SQL> alter tablespace dbuser online;

I think that this will help all of us in the feature....

No comments:

Post a Comment