Wikipedia

Search results

Moving/Renaming Datafiles While the Database is Online

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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[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.
1
2
3
4
5
6
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.
1
2
3
4
5
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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.