Moving & renaming datafile

Sometimes we need to rename or move data files.

My db version: 11.2.0.3

One of the easy way is below:

1. Identify which data file you will move. In my case I will move datafile which belong FCATDEV121 tablespace.

select a.tablespace_name, a.status, file_name from dba_tablespaces a, dba_data_files b
where a.tablespace_name=b.tablespace_name
order by 1;

TABLESPACE_NAME       STATUS FILE_NAME
==========================================================================================================================
SYSAUX       ONLINE /u01/app/oracle/oradata/FCDBT/sysaux01.dbf
SYSTEM       ONLINE /u01/app/oracle/oradata/FCDBT/system01.dbf
UNDOTBS1       ONLINE /u01/app/oracle/oradata/FCDBT/undotbs01.dbf
USERS       ONLINE /u01/app/oracle/oradata/FCDBT/users01.dbf
FCATDEV121       ONLINE /home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8



2. Take tablespace to offline mode, copy using OS command (cp, mv), rename datafile and take tablespace to online mode

SQL> ALTER TABLESPACE FCAT_DEV21 offline;

Tablespace altered.



SQL> host
[oracle@fc-db-tst1 FCDBT_EXPTPS]$ mv /home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf  /u01/app/oracle/oradata/FCDBT/FCAT_DEV21_01.dbf
[oracle@fc-db-tst1 FCDBT_EXPTPS]$ exit
exit

SQL> ALTER TABLESPACE FCAT_DEV21 RENAME DATAFILE '/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf'  TO '/u01/app/oracle/oradata/FCDBT/FCAT_DEV21_01.dbf';

Tablespace altered.

SQL> ALTER TABLESPACE FCAT_DEV21 online;

3. Check

SQL> select a.tablespace_name, a.status, file_name from dba_tablespaces a, dba_data_files b
where a.tablespace_name=b.tablespace_name
order by 1;  2    3

TABLESPACE_NAME       STATUS FILE_NAME
------------------------------ --------- --------------------------------------------------------------FCAT_DEV21       ONLINE /u01/app/oracle/oradata/FCDBT/FCAT_DEV21_01.dbf
SYSAUX       ONLINE /u01/app/oracle/oradata/FCDBT/sysaux01.dbf
SYSTEM       ONLINE /u01/app/oracle/oradata/FCDBT/system01.dbf
UNDOTBS1       ONLINE /u01/app/oracle/oradata/FCDBT/undotbs01.dbf
USERS       ONLINE /u01/app/oracle/oradata/FCDBT/users01.dbf

Starting 12.c we can move datafiles in online mode!

ALTER DATABASE MOVE DATAFILE
'/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf'
TO '/u01/app/oracle/oradata/FCDBT/FCAT_DEV21_01.dbf';