Move or rename a data file in Oracle

posted Sep 8, 2010, 8:40 AM by Sachchida Ojha

Shutdown the database.
Rename the physical file on the OS.
Start the database in mount mode.
Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
Open the database.

SQL> SELECT name FROM v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/MJBDB/oradata/system.dbf
/u04/MJBDB/oradata/undotbs01.dbf
/u04/MJBDB/oradata/undotbs02.dbf
/u02/MJBDB/oradata/aud01.dbf
/u27/MJBDB/oradata/ts_dyn_agg_data1.dbf
/u02/MJBDB/oradata/drsys01.dbf
/u02/MJBDB/oradata/indx01.dbf
/u02/MJBDB/oradata/developers.dbf
/u12/MJBDB/oradata/    ts_mjbagg_idx_04.dbf
 


The following SQL*Plus output shows how this is done.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> HOST MV /u12/MJBDB/oradata/    ts_mjbagg_idx_04.dbf /u12/MJBDB/oradata/ts_mjbagg_idx_04.dbf
*** removed TAB character.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL>ALTER DATABASE RENAME FILE ‘/u12/MJBDB/oradata/    ts_mjbagg_idx_04.dbf' TO ‘/u12/MJBDB/oradata/ts_mjbagg_idx_04.dbf’; Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL>Repeating the initial query shows that the the datafile has been renamed in the data dictionary.


SQL> SELECT name FROM v$datafile; 
NAME--------------------------------------------------------------------------------
/u02/MJBDB/oradata/system.dbf
/u04/MJBDB/oradata/undotbs01.dbf
/u04/MJBDB/oradata/undotbs02.dbf
/u02/MJBDB/oradata/aud01.dbf
/u27/MJBDB/oradata/ts_dyn_agg_data1.dbf
/u02/MJBDB/oradata/drsys01.dbf
/u02/MJBDB/oradata/indx01.dbf
/u02/MJBDB/oradata/developers.dbf
/u12/MJBDB/oradata/ts_mjbagg_idx_04.dbf’

Comments