MOVE DATA FILE FROM X LOCATION TO Y LOCATION

The section offers some procedures for renaming and relocating datafiles in a single tablespace. You must have the ALTER TABLESPACE system privilege to rename datafiles of a single tablespace.

1. The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the users tablespace:

2. SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';

3. Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.

ALTER TABLESPACE USERS OFFLINE NORMAL;

/*ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF' OFFLINE NORMAL;*/

4. Copy the datafiles to their new locations and rename them using the operating system.

COPY C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF TO C:\ORACLE\ORADATA\USERS01.DBF

5. Rename the datafiles within Oracle. The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

If the tablespace is offline but the database is open, use the ALTER TABLESPACE ... RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE ... RENAME FILE statement.

ALTER TABLESPACE users RENAME DATAFILE 'C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF' TO 'C:\ORACLE\ORADATA\USERS01.DBF';

6. Bring the tablespace online, or open the database. If the users tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.

ALTER TABLESPACE USERS ONLINE;

/*ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\USERS01.DBF' ONLINE;*/

7. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

How to move system tablespace datafile

You can use one of two techniques as below.

1. Shutdown, move the file, re-create the control file, and startup.
2. Shutdown, stat up in mount mode, move them, and then open the database.

Here I am demonstrating 2^nd option because its easy.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>startup mount;

ORACLE instance started.

System Global Area 122754516 bytes

---

---

---

Database mounted.

SQL> !copy ‘c:\oracle\ora92\oradata\nikunj\system.dbf’ to ‘c:\oracle\ora92\oradata\system.dbf’

SQL>alter database rename file ‘c:\oracle\ora92\oradata\nikunj\system.dbf’ to ‘c:\oracle\ora92\oradata\system.dbf’;

Database altered.

SQL>alter database open;

Database altered.

Backup your control file again, and that’s it.