How to Move, Rename and Relocate Datafiles in Oracle

• [email protected]
• 07 Jun, 2025
• 0 Comments
• 2 Mins Read

How to Move, Rename and Relocate Datafiles in Oracle

Move Datafile in Oracle

You can move datafile from ASM to File System Online as follows.

ALTER DATABASE MOVE DATAFILE '+CRS/PRIM/DATAFILE/users.876908629' to '/data/oradata/users.dbf';

ALTER DATABASE MOVE DATAFILE '/data/oradata/users.dbf' to '+CRS/PRIM/DATAFILE/users';

You can move datafile from File System to File system Online as follows.

ALTER DATABASE MOVE DATAFILE '/data/oradata/system01.dbf' TO '/data/oradata/new_location/system01.dbf';

Rename Datafile

ALTER TABLESPACE TABLESPACE_NAME OFFLINE NORMAL;

After Offline operation is completed, rename datafile with mv command from ‘users.dbf’ to ‘users01.dbf’.

execute the following commands to rename datafile in Controlfile and make it online again.

ALTER TABLESPACE TABLESPACE_NAME name RENAME DATAFILE /data/oradata/users.dbf' TO /data/oradata/users01.dbf';

ALTER TABLESPACE TABLESPACE_NAME ONLINE;

 Relocate datafiles

You can relocate datafiles to new location because of insufficent disk space.

SQL> SELECT name FROM v$datafile;

NAME
---------------------------------------------------------
/data/oradata/SYSTEM01.DBF
/data/oradata/UNDOTBS01.DBF
/data/oradata/SYSAUX01.DBF
/data/oradata/USERS01.DBF
/data/oradata/TBS01.DBF

5 rows selected.

SQL>

Shutdown database.

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

Move physical datafile to the new disk with mv command.

[oracle@node2]/home/oracle $ mv /data/oradata/TBS01.DBF  /data/oradata

Startup database mount mode.

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.

Rename datafile in Controlfile as follows.

SQL> ALTER DATABASE RENAME FILE '/data/oradata/TBS01.DBF' TO '/u02/oradata/TBS01.DBF';

Database altered.

Now Open database.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

List the datafiles again.

SQL> SELECT name FROM v$datafile;

NAME
---------------------------------------------------------
/u01/oradata/SYSTEM01.DBF
/u01/oradata/UNDOTBS01.DBF
/u01/oradata/SYSAUX01.DBF
/u01/oradata/USERS01.DBF
/u02/oradata/TBS01.DBF

5 rows selected.

SQL>

—————————————————————————————————————————————–

At Learnomate Technologies, we take pride in offering the most practical, real-time, and in-depth Oracle DBA training, including advanced topics like Multitenant Architecture. Our hands-on sessions, expert trainers, and real-world use cases ensure you’re not just learning theory, but gaining industry-ready expertise.

👉 For deeper insights and technical tutorials, visit our YouTube channel: 🔗 www.youtube.com/@learnomate

👉 Explore our full course offerings and get in touch via our official website: 🔗 www.learnomate.org

👉 Follow me on LinkedIn for daily knowledge bombs, career tips, and student success stories: 🔗 Ankush Thavali – LinkedIn

If you want to read more about different technologies, tools, and career-focused content, check out our blog page here: 🔗 https://learnomate.org/blogs/

The future is multitenant—get skilled, get certified, and get ahead. See you in the batch!