DBA Hub

📋Steps in this guide1/6

How to Move, Rename and Relocate Datafiles in Oracle

How to Move, Rename and Relocate Datafiles in Oracle

oracle configurationintermediate
by OracleDba
18 views
1

Overview

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

Code/Command (click line numbers to comment):

1
2
3
4
5
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';

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

Section 2

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. You can relocate datafiles to new location because of insufficent disk space.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
ALTER TABLESPACE TABLESPACE_NAME OFFLINE NORMAL;

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

ALTER TABLESPACE TABLESPACE_NAME ONLINE;

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>
3

Section 3

Shutdown database. Move physical datafile to the new disk with mv command. Startup database mount mode.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

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

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.
4

Section 4

Rename datafile in Controlfile as follows. Now Open database. List the datafiles again.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> ALTER DATABASE RENAME FILE '/data/oradata/TBS01.DBF' TO '/u02/oradata/TBS01.DBF';

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

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>
5

Section 5

—————————————————————————————————————————————– 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/
6

Section 6

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!