POiSON WORLD

Renaming datafile (.dbf) rename in 11g

Rename Datafiles in Single Tablespace (Database Open Mode) :

Caution : Backup your tablespace Before you will do some change in your tablespace.

Please find below steps to rename datafile.

We can use the alter tablespace rename datafile command,
but the tablespace most be offline and you must re-name at OS level command the data file while the tablespace is offline:

Step: 1

[[email protected] ~]$ sqlplus / as sysdba

SQL> ALTER TABLESPACE SYSAUX OFFLINE NORMAL;
Tablespace altered.

SQL> !

Step: 2

Rename undo07.dbf to sysaux08.dbf

[[email protected] ~]$ cd /u01/db/apps_st/data/

[[email protected] data]$ ls -lrt|grep undo07.dbf
-rw-r----- 1 oraqa dba 3221233664 Aug 9 11:32 undo07.dbf

[[email protected] data]$ chmod 755 undo07.dbf

[[email protected] data]$ mv undo07.dbf sysaux08.dbf

[[email protected] data]$ ls -lrt|grep undo07.dbf

[[email protected] data]$ ls -lrt|grep sysaux08.dbf
-rwxr-xr-x 1 oraqa dba 3221233664 Aug 9 11:32 sysaux08.dbf

Step: 3

[[email protected] data]$ sqlplus / as sysdba

SQL> ALTER TABLESPACE SYSAUX RENAME DATAFILE '/u01/db/apps_st/data/undo07.dbf' to '/u01/db/apps_st/data/sysaux08.dbf';

Tablespace altered.

Step: 4

SQL> ALTER TABLESPACE SYSAUX ONLINE;

Tablespace altered.

Steps: 5

Verify tablespace status.

SQL> SELECT status
FROM dba_tablespaces
WHERE tablespace_name = 'SYSAUX';

STATUS
---------
ONLINE

Verify datafile name.

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME = 'SYSAUX';

TABLESPACE_NAME-------------FILE_NAME
--------------------------------------------------------------------------------
SYSAUX /u01/db/apps_st/data/sysaux08.dbf

Rename datafile undo07.dbf to sysaux08.dbf completed.