How to Move or Rename an Oracle Datafile in Oracle Database

Leave a reply

In Oracle databases, there are times when you need to move or rename a datafile — for example, when changing storage paths, performing maintenance, or reorganizing tablespaces. This operation can be done online or offline depending on the tablespace status.

In this blog, we will cover:

  • Prerequisites
  • Moving datafile using ALTER DATABASE
  • Moving datafile using RMAN
  • Checking the status
  • Common troubleshooting tips

Following are the steps for move or rename the Oracle Datafile:

1: Login to the database as SYSDBA and check the file location

sqlplus sys as sysdba
SELECT file_id, file_name, tablespace_name 
FROM dba_data_files
ORDER BY tablespace_name;

2: Make the tablespace offline which file need to rename:

SELECT tablespace_name, status FROM dba_tablespaces;

ALTER TABLESPACE users OFFLINE;

3: Copy the datafile to the required location at the OS level

mv /u01/oradata/ORCL/users01.dbf /u02/oradata/ORCL/users01.dbf

4: Rename the datafile using Alter Database command

ALTER DATABASE RENAME FILE 
'/u01/oradata/ORCL/users01.dbf' 
TO 
'/u02/oradata/ORCL/users01.dbf';

Step 5: Make the tablespace online.

ALTER TABLESPACE users ONLINE;

The above mentioned steps is not worked for system , temp and undo tablespaces as they cannot be taken offline.
Normally we drop and recreate the undo/temp tablespaces rather than moving the datafiles as it doesn’t involve any downtime.

Following are the steps to move system datafiles:

Steps to move SYSTEM datafile from one location to another:

1: Login to the database as SYSDBA

Sqlplus / as sysdba

Step 2: Shutdown the database

Shutdown immediate

Step 3: Copy/rename the datafile at the OS level

mv /u01/oradata/ORCL/system01.dbf /u02/oradata/ORCL/system01.dbf

Step 4: Start the database at MOUNT state.
At this stage control file is read.

STARTUP MOUNT;

Step 5: Rename the datafile using Alter Database command

ALTER DATABASE RENAME FILE 
'/u01/oradata/ORCL/system01.dbf' 
TO 
'/u02/oradata/ORCL/system01.dbf';

Step 6: Open the database

ALTER DATABASE OPEN;

Move Tempfile:

Tempfile need to dropped and created at new location for moving purpose. Use the following link to move temp file by simply recreate it :
Manage the Temp Tablespace

Leave a Reply