Move / Rename Datafile

Easy Steps to Move / Rename Data file 

Catagory :- Database Administrator

It is possible to move and or rename datafiles while the database is online provided the tablespace in which the files belong is a non SYSTEM tablespace and does not contain any active ROLLBACK or TEMPORARY segments.

This document will detail the steps to move/rename a datafile using Oracle 11g R2 on Solaris. These steps also apply with 10g.

Commands to be used

1. select file_name from dba_data_files;
2. select FILE_NAME, TABLESPACE_NAME, STATUS from dba_data_files where tablespace_name='MTANDON1';
3. ALTER TABLESPACE MTANDON1 OFFLINE;
4. ALTER TABLESPACE MTANDON1 ONLINE;
5. select NAME, STATUS from v$datafile where NAME='/uc52/mtandev/mtandond2data/MTANDON1E.dbf';

Steps to follow

1. Connect the Database as SYS DBA

sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 2 10:50:08 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2. Check the status and file you want to move or rename associated with Tablespace as under

SQL> select FILE_NAME, TABLESPACE_NAME, STATUS from dba_data_files where tablespace_name='MTANDON1';

FILE_NAME                                                    TABLESPACE_NAME                STATUS

------------------------------------------------------------ ------------------------------ ----------

/uc52/mtandev/mtandond2data/MTANDON1E.dbf                        MTANDON1                         AVAILABLE

/uc52/mtandev/mtandond2data/MTANDON1D.dbf                        MTANDON1                         AVAILABLE

/uc52/mtandev/mtandond2data/MTANDON1E.dbf                         MTANDON1                         AVAILABLE

3. The first step is to take the tablespace in which the file(s) to moved/renamed are a member offline.

SQL> ALTER TABLESPACE MTANDON1 offline;

Tablespace altered.

4. Check the Status of Datafile iis offline now

SQL> col NAME format a60

SQL> select NAME, STATUS from v$datafile where NAME='/uc52/mtandev/mtandond2data/MTANDON1E.dbf;

NAME                                                                                                        STATUS

------------------------------------------------------------                                   ----------

/uc52/mtandev/mtandond2data/MTANDON1E.dbf                        OFFLINE

/uc53/mtandev/mtandond2data $ mv /uc52/mtandev/mtandond2data/MTANDON1E.dbf  /uc53/mtandev/mtandond2data/MTANDON1E.dbf 

5. It might take few min, depending on size of your File.

SQL> alter database rename file '/uc52/mtandev/mtandond2data/MTANDON1E.dbf' to '/uc53/mtandev/mtandond2data/MTANDON1E.dbf';

Database altered.

6. Last thing to do, bring the tablespace back online.

SQL> ALTER TABLESPACE MTANDON1 online;

Tablespace altered.

7. Check Status

SQL> select NAME, STATUS from v$datafile where NAME='/uc53/mtandev/mtandond2data/MTANDON1E.dbf';

NAME                                                                                                    STATUS

------------------------------------------------------------                                  ----------

/uc53/mtandev/mtandond2data/MTANDON1E.dbf                        ONLINE

SQL> 

The move/rename is complete.