how to rename a tablespace and datafiles in oracle databse

how to rename a tablespace and datafiles in oracle databse

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

6 rows selected.


SQL> create tablespace payroll datafile 'e:\payroll.dbf' size 10m;

Tablespace created.


SQL> select name from v$datafile;

NAME
----------------------------------------
E:\DATA\TEST\SYSTEM01.DBF
E:\DATA\TEST\SYSAUX01.DBF
E:\DATA\TEST\UNDOTBS01.DBF
E:\DATA\TEST\USERS01.DBF
E:\DATA\TEST\EXAMPLE01.DBF
E:\PAYROLL.DBF

6 rows selected.

SQL>


SQL> alter tablespace payroll rename to payme;

Tablespace altered.


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
PAYME

SQL> select tablespace_name,file_name from dba_data_files where tablespace_name ='PAYME';

TABLESPACE_NAME FILE_NAME
-------------------- --------------------
PAYME E:\PAYROLL.DBF


rename datafile


SQL> alter database rename file 'e:\payroll.dbf' to 'e:\payme.dbf';
alter database rename file 'e:\payroll.dbf' to 'e:\payme.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 6 - file is in use or recovery
ORA-01110: data file 6: 'E:\PAYROLL.DBF'

SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 176161684 bytes
Database Buffers 352321536 bytes
Redo Buffers 5844992 bytes
Database mounted.
SQL> alter database rename file 'e:\payroll.dbf' to 'e:\payme.dbf';
alter database rename file 'e:\payroll.dbf' to 'e:\payme.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 6 - new file 'e:\payme.dbf' not found
ORA-01110: data file 6: 'E:\PAYROLL.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> HOST
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Mani>E:

E:\>move PAYROLL.DBF PAYROME.DBF
1 file(s) moved.

E:\>EXIT

SQL> alter database rename file 'e:\payroll.dbf' to 'e:\payROME.dbf';

Database altered.



SQL> select tablespace_name,file_name from dba_data_files where tablespace_name ='PAYME';

TABLESPACE_NAME FILE_NAME
-------------------- --------------------
PAYME E:\PAYROME.DBF
other way is to take the datafile offline and then rename it(this will need recovery of file)
and other way is to take backup of control file to trace and shu immideiate and make the changes
in the created file this will also need downtime.