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.
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