How do you rename a datafile in Oracle?

Answer 1

â–º Rename a Datafile in Oracle(10g):-SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/product/10.2.0/oradata/abab/system.dbf

/u01/app/oracle/product/10.2.0/oradata/abab/undo.dbf

/u01/app/oracle/product/10.2.0/oradata/abab/sysaux.dbf

/u01/app/oracle/product/10.2.0/oradata/abab/usr01.dbf

/u01/app/oracle/product/10.2.0/oradata/abab/usr02.dbf

/u01/app/oracle/product/10.2.0/oradata/abab/asm01.dbf

/u01/app/oracle/product/10.2.0/oradata/abab/usr04.dbf

/u01/app/oracle/product/10.2.0/oradata/abab/m123.dbf

8 rows selected.

SQL> alter tablespace msb read only;

Tablespace altered.

SQL> select TABLESPACE_NAME, status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USR01 ONLINE

USR02 ONLINE

TEMP01 ONLINE

INNER ONLINE

ASM ONLINE

MSB READ ONLY

TEMPORARY ONLINE

TABLESPACE_NAME STATUS

------------------------------ ---------

TSPUNDO ONLINE

12 rows selected.

SQL> alter tablespace msb offline;

Tablespace altered.

============================================================

Work on OS level:-

[oracle@abab-YYY-XXX abab]$ CD /u01/app/oracle/product/10.2.0/oradata/nisi/

[oracle@abab-YYY-XXX abab]$ ls

asm01.dbf redo1.log sysaux.dbf undo.dbf usr04.dbf

m123.dbf redo2.log system.dbf usr01.dbf

redo3.log temporary.dbf usr02.dbf

[oracle@abab-YYY-XXX abab]$ cp m123.dbf mmm.dbf

[oracle@abab-YYY-XXX abab]$ ls

asm01.dbf redo1.log sysaux.dbf undo.dbf usr04.dbf

m123.dbf redo2.log system.dbf usr01.dbf

mmm.dbf redo3.log temporary.dbf usr02.dbf

[oracle@abab-YYY-XXX abab]$

============================================================

SQL> alter database rename file

2 '/u01/app/oracle/product/10.2.0/oradata/abab/m123.dbf'

3 to

4 '/u01/app/oracle/product/10.2.0/oradata/abab/mmm.dbf';

Database altered.

SQL> alter tablespace msb online;

Tablespace altered.

SQL> alter tablespace msb read write;

Tablespace altered.

SQL> select name, status from v$datafile;

NAME STATUS

------------------------------------------------------------ -------

/u01/app/oracle/product/10.2.0/oradata/abab/system.dbf SYSTEM

/u01/app/oracle/product/10.2.0/oradata/abab/undo.dbf ONLINE

/u01/app/oracle/product/10.2.0/oradata/abab/sysaux.dbf ONLINE

/u01/app/oracle/product/10.2.0/oradata/abab/usr01.dbf ONLINE

/u01/app/oracle/product/10.2.0/oradata/abab/usr02.dbf ONLINE

/u01/app/oracle/product/10.2.0/oradata/abab/asm01.dbf ONLINE

/u01/app/oracle/product/10.2.0/oradata/abab/usr04.dbf ONLINE

/u01/app/oracle/product/10.2.0/oradata/abab/mmm.dbf ONLINE

8 rows selected.