Rename Datafile (1/2)

Rename Datafile (1/2) [You are here]
Rename Datafile (2/2)

There are 2 ways of renaming datafile (relocating file to another directory also consider as renaming datafile, because the name of the file is including the full path of the file)

1. Rename datafile while database is OPEN

To rename datafile while database is open first you must bring the tablespace that related to the datafile OFFLINE. There are two ways to do that

1.1 Procedure renaming datafile while database is OPEN:

- Alter the tablespace to READ ONLY mode
- Alter the tablespace to OFFLINE
- Rename the datafile from OS
- Alter database or alter tablespace to the rename file command
- Alter the tablespace to ONLINE
- Alter the tablespace to READ WRITE mode

In this example my objective is to change the my_tbs_02.dbf to my_tbs_2000.dbf

Now I show you step by step, first bring the tablespace to read only mode.

1. Alter the tablespace to READ ONLY mode

ALTER TABLESPACE my_tbs READ ONLY;

the STATUS now are READ ONLY

2. Alter the tablespace to OFFLINE

ALTER TABLESPACE my_tbs OFFLINE;

The reason you are not see the STATUS OFFLINE is because the tablespace was in READ ONLY mode, however you can see the change that once it OFFLINE the BYTES column are nil.


3. Rename the datafile from OS

mv /datafiles/edba01/edba01/my_tbs_02.dbf /datafiles/edba01/edba01/my_tbs_2000.dbf

If you want to execute OS command without exit from SQL prompt you can include ! infront of your command, for windows user you can use HOST, for example SQL> host dir /w


4. Alter database or alter tablespace to the rename file command

There are two methods of renaming datafile, you may can choose one from here.

ALTER DATABASE RENAME FILE '/datafiles/edba01/edba01/my_tbs_02.dbf' TO '/datafiles/edba01/edba01/my_tbs_2000.dbf';

or

ALTER TABLESPACE my_tbs RENAME DATAFILE '/datafiles/edba01/edba01/my_tbs_02.dbf' TO '/datafiles/edba01/edba01/my_tbs_2000.dbf';


I am using the first one

If you do not change the OS filename as step 3 Oracle will give you error, because Oracle will search for the new filename, and Oracle required the file to be exists.


5. Alter the tablespace to ONLINE

ALTER TABLESPACE my_tbs ONLINE;

6. Alter the tablespace to READ WRITE mode

ALTER TABLESPACE my_tbs READ WRITE;

Now I have the filename changed.

Rename Datafile (1/2) [You are here]
Rename Datafile (2/2)