Rename Tablespace and Datafile 

Rename tablespace is quite straight forward, as you could expect

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;

Once the tablespace is rename, many a times we would require to change the datafiles associated with the tablespace name. This could be because of organization policy or may be because of senior DBA insists on that.

To rename a datafile we have to follow below logical steps in given sequence --

- Take tablespace offline
    ALTER TABLESPACE tablespace_name OFFLINE;
 
- Physically rename the file using operating system command prompt
    mv /database/oracle/CLYP/data/DAT_001.dbf /database/oracle/CLYP/data/DAT_005_CLYP_001.dbf
 
- Rename the datafiles internally on SQL command prompt. This will make oracle to access the underlying datafile when the particular tablespace is addressed
    ALTER TABLESPACE tablespace_name RENAME DATAFILE '/database/oracle/CLYP/data/DAT_001.dbf' to '/database/oracle/CLYP/data/DAT_005_CLYP_001.dbf';
 
- Take tablespace online
    ALTER TABLESPACE tablespace_name ONLINE;

SQL used to genereate the script is as follows --

select 'ALTER TABLESPACE '
       ||tablespace_name
       ||' OFFLINE; '
       ||'!mv '||file_name||' '||replace(file_name,'REC1','CLYP')||'; '
       ||'ALTER TABLESPACE '
       ||tablespace_name
       ||' RENAME DATAFILE '''
       ||file_name
       ||''' to '''
       ||replace(file_name,'REC1','CLYP')
       ||''';'
       ||' ALTER TABLESPACE '
       ||tablespace_name
       ||' ONLINE;'
 from dba_data_files
where tablespace_name like '%CLYP'
order by tablespace_name;


Please note, the above script need to be altered before using it. This script was developed considering certain granules and environment details. The whole purpose of this SQL script is for the illustration that generating shell/scripts using the SQL queries can save repetitive work and will help in reducing typo errors.