Tuesday 30 August 2016

How to rename a datafile without extension .dbf to datafile with extension .dbf?

For example i added a datafile.but forgotten to give the extension .dbf then how to rename it?

SQL> alter tablespace room add datafile '/u01/app/oracle/room' size 200m;

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/system.dbf
/u01/app/oracle/sysaux.dbf
/u01/app/oracle/undotbs.dbf
/u01/app/oracle/userdata.dbf
/u01/app/oracle/system1.dbf
/u01/app/oracle/room2.dbf
/u01/app/oracle/room3.dbf
/u01/app/oracle/room

8 rows selected.

Here a new datafile room is created without extension .dbf  Then shutdown the database.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
Database mounted.
If we shutdown and mount it people cannot acess the data in the database.here database is offline so datafiles are also offline.

SQL> !cp /u01/app/oracle/room  /u01/app/oracle/roomone.dbf

SQL> !ls /u01/app/oracle/room*
/u01/app/oracle/room  /u01/app/oracle/room2.dbf  /u01/app/oracle/roomone.dbf

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/system.dbf
/u01/app/oracle/sysaux.dbf
/u01/app/oracle/undotbs.dbf
/u01/app/oracle/userdata.dbf
/u01/app/oracle/system1.dbf
/u01/app/oracle/room2.dbf
/u01/app/oracle/room3.dbf
/u01/app/oracle/room

8 rows selected.
Here still it is showing the room datafile with out extension.dbf we need to rename in offline.

SQL> alter database rename file '/u01/app/oracle/room' to '/u01/app/oracle/roomone.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/system.dbf
/u01/app/oracle/sysaux.dbf
/u01/app/oracle/undotbs.dbf
/u01/app/oracle/userdata.dbf
/u01/app/oracle/system1.dbf
/u01/app/oracle/room2.dbf
/u01/app/oracle/room3.dbf
/u01/app/oracle/roomone.dbf

8 rows selected.
here it is changed from room to roomone.dbf

SQL> !ls /u01/app/oracle/room*    
/u01/app/oracle/room  /u01/app/oracle/room2.dbf  /u01/app/oracle/roomone.dbf

SQL> !rm /u01/app/oracle/room     

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/system.dbf
/u01/app/oracle/sysaux.dbf
/u01/app/oracle/undotbs.dbf
/u01/app/oracle/userdata.dbf
/u01/app/oracle/system1.dbf
/u01/app/oracle/room2.dbf
/u01/app/oracle/room3.dbf
/u01/app/oracle/roomone.dbf

8 rows selected.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE






No comments:

Post a Comment