Rename or Move Oracle datafiles

One of my juinor dba created the files without extension and I really dont like that, also I wanted to blog about this from quite sometime so here it goes:

Start Sqlplus and logon as sysdba

[oracle@**** ****]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 26 13:20:34 2011
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
SQL> conn sys / as sysdba
Enter password:
Connected.
SQL>

Select files and tablespace name

SQL> select file_name, tablespace_name from dba_data_files;
FILE_NAME                TABLESPACE_NAME
======================================================
/u01/oracle/oradata/****/users01.dbf    USERS
/u01/oracle/oradata/****/undotbs01.dbf    UNDOTBS1
/u01/oracle/oradata/****/sysaux01.dbf    SYSAUX
/u01/oracle/oradata/****/system01.dbf    SYSTEM
/u01/oracle/oradata/****/example01.dbf    EXAMPLE
/u01/oracle/oradata/****/census2005    CENSUS2005
/u01/oracle/oradata/****/census2010.dbf    CENSUS2010
7 rows selected.

Take the tablespace offline

SQL> ALTER TABLESPACE CENSUS2005 OFFLINE NORMAL;
Tablespace altered.

Rename the file manually using operating system command (Use “$” if you are not windows)

SQL> !pwd
/u01/oracle/oradata/****
SQL> !mv census2005 census2005.dbf

Alter the tablespace to reflect changes in controlfile

SQL> ALTER TABLESPACE CENSUS2005 RENAME DATAFILE
 2  '/u01/oracle/oradata/****/census2005'
 3  TO '/u01/oracle/oradata/****/census2005.dbf'
 4  /
Tablespace altered.

NOTE: The file is CaSe SeNsiTiVe

Bring the tablespace online

SQL> ALTER TABLESPACE CENSUS2005 online;
Tablespace altered.