*****************************************************
* Description: Oracle Data File Relocation Procedure
* Compatiablity: RDBMS 10g, 11g, 12c
* Date: 18:03 PM EST, 01/16/2017
*****************************************************


<1> Dynamic SQL for querying target data file name:
    |
    |__ SQL> select 'cp ' || FILE_NAME || ' ' || '/u01/target_location' FROM DBA_DATA_FILES WHERE tablespace_name in ('RASLEG_DATA02','RASLEG_INDX02');
	
             cp /orastage01/WCS/ijet_tmd_data01.dbf /orabackup/WCS/datafiles/ijet_tmd_data01.dbf
             cp /orastage01/WCS/ijet_tmd_data02.dbf /orabackup/WCS/datafiles/ijet_tmd_data02.dbf
             cp /orastage01/WCS/rasleg_data02_07.dbf /orabackup/WCS/datafiles/rasleg_data02_07.dbf
             cp /orastage01/WCS/rasleg_data02_06.dbf /orabackup/WCS/datafiles/rasleg_data02_06.dbf

			 
<2> Dynamic SQL for renaming data file to be registered within database: 
    |
    |__ SQL> select 'ALTER DATABASE RENAME FILE ' || '''' || FILE_NAME || '''' || ' TO ' || '''' || FILE_NAME || '''' 
             FROM DBA_DATA_FILES WHERE tablespace_name in ('RASLEG_DATA02','RASLEG_INDX02');

             ALTER DATABASE RENAME FILE '/orastage01/WCS/ijet_tmd_data01.dbf' TO '/orabackup/WCS/datafiles/ijet_tmd_data01.dbf';
             ALTER DATABASE RENAME FILE '/orastage01/WCS/ijet_tmd_data02.dbf' TO '/orabackup/WCS/datafiles/ijet_tmd_data02.dbf';
             ALTER DATABASE RENAME FILE '/orastage01/WCS/rasleg_data02_07.dbf' TO '/orabackup/WCS/datafiles/rasleg_data02_07.dbf';
             ALTER DATABASE RENAME FILE '/orastage01/WCS/rasleg_data02_06.dbf' TO '/orabackup/WCS/datafiles/rasleg_data02_06.dbf';
			 

<3> Shutdown database:
    |
    |__ SQL> shutdown immediate;
	
	
<4> Startup database to mount mode:
    |
    |__ SQL> startup mount;
	

<5> Execute data file renaming in Step 2:


<6> Alter database to mount mode:
    |
    |__ SQL> alter database open;