Moving Or Renaming Datafile

In this article i will move and rename one datafile. This example are based on a Oracle 10g installation on Windows, but this method is the same for different versions of Oracle on any platform.

The current location of the datafiles can be queried from the v$datafile.

SQL> SELECT file#,name, status FROM v$datafile;

FILE# NAME STATUS
—– ——————————————————- ———-
1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF SYSTEM
2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF ONLINE
3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF ONLINE
4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF ONLINE
5 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF ONLINE

USERS01.DBF will be moved. For this , database will be shutdown.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

USERS01.DBF physical file is moved to new location. OS commands are used for this operation. D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF is moved and renamed to C:\orcl\oradata\moved_USERS01.DBF.

Database is started up in mount mode.
SQL> startup mount
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 1296500 bytes
Variable Size 146802572 bytes
Database Buffers 159383552 bytes
Redo Buffers 7090176 bytes
Database mounted.

SQL> alter database rename file ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF’ to ‘C:\orcl\oradata\moved_USERS01.DBF’;

Database altered.
Data dictionary has been updated.

SQL> alter database open;

Database altered.

initial query shows that the the datafile has been renamed in the data dictionary.
SQL> SELECT file#,name, status FROM v$datafile;

FILE# NAME STATUS
—– ——————————————————- ———-
1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF SYSTEM
2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF ONLINE
3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF ONLINE
4 C:\ORCL\ORADATA\MOVED_USERS01.DBF ONLINE
5 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF ONLINE

Also you can do this when the database is open, by offlining the corresponding tablespace. But before do this when database is open, make sure you have valid backup of database and archivelog.

Best Regards,

References:
1 – Oracle® Database SQL Reference 10g Release 2 (10.2)
2 – Metalink
3 – Oracle® Database Backup and Recovery Advanced User’s Guide 10g Release 2 (10.2)