Monday, March 3, 2014

How to rename or move a datafile

1)shutdown immediate

2)SQL> startup mount  (bring the database in mount stage)

2a)move or rename the file to desired location/name at os level.
Example: mv PATH1/data01.dbf PATH2/data01.dbf #(for move)
         or
         mv PATH1/data01.dbf PATH1/user01.dbf #(for rename)

3)SQL> alter database rename file 'PATH1/data01.dbf' to 'PATH2/data01.dbf';
  or
  SQL> alter database rename file 'PATH1/data01.dbf' to 'PATH1/user01.dbf';

4)SQL> aler database open;