It is possible to move and or rename datafiles while the database is online provided the tablespace in which the files belong is a non SYSTEM tablespace and does not contain any active ROLLBACK or TEMPORARY segments.
This document will detail the steps to move/rename a datafile using Oracle 11g R2 on Linux. These steps also apply with 10g.
The datafile for the TEST tablespace is in the wrong directory. The file should be in /u02/app/oracle/oradata/orcl.
SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u02/app/oracle/oradata/orcl/users01.dbf /u02/app/oracle/oradata/orcl/undotbs01.dbf /u02/app/oracle/oradata/orcl/sysaux01.dbf /u02/app/oracle/oradata/orcl/system01.dbf /u02/app/oracle/oradata/orcl/example01.dbf /u02/app/oracle/oradata/test/test.dbf 6 rows selected. SQL>
The first step is to take the tablespace in which the file(s) to moved/renamed are a member offline.
[oracle@ora1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 12 09:28:58 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter tablespace test offline; Tablespace altered. SQL>
Next we move the file using operating system commands.
SQL> host [oracle@ora1 ~]$ mv /u02/app/oracle/oradata/test/test.dbf /u02/app/oracle/oradata/orcl/test.dbf [oracle@ora1 ~]$ exit exit SQL>
Now we need to update the data dictionary and the control. We will use the ALTER DATABASE RENAME FILE statement to perform those actions.
SQL> alter database rename file '/u02/app/oracle/oradata/test/test.dbf' to '/u02/app/oracle/oradata/orcl/test.dbf'; Database altered. SQL>
Last thing to do is bring the tablespace back online.
SQL> alter tablespace test online; Tablespace altered. SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u02/app/oracle/oradata/orcl/users01.dbf /u02/app/oracle/oradata/orcl/undotbs01.dbf /u02/app/oracle/oradata/orcl/sysaux01.dbf /u02/app/oracle/oradata/orcl/system01.dbf /u02/app/oracle/oradata/orcl/example01.dbf /u02/app/oracle/oradata/orcl/test.dbf 6 rows selected. SQL>
The move/rename is complete.
Good post
Good and explained in a simple way
Nice post
perfect and simple post
dats good…
Excelent
Very nice post
I did try this way and this is not good way to relocation datafile, because I had issue using this way
Thank you
Shouldn’t the database be in archive log mode for doing this ?
nice post…explained in simple way….
oracle 12c., datafile movement is improved…no need to put the table space in offline.
when i execute the HOST MOVE command it is renaming the given file,is it necessary to execute the next following command that is “ALTER DATABASE RENAME FILE”
If you get this error:
ERROR at line 1:
ORA-01113: file XXX needs media recovery
You need t:
recover datafile ‘/u02/app/oracle/oradata/orcl/test.dbf’
Very easy practical steps. It Works well
Really good one explained very simpler manner.
This is applicable if the database is in archivelog mode.