How to Rename DataFile in Oracle

How to Rename DataFile in Oracle:

The procedure of renaming an Oracle datafile is actually the same as the procedure of moving datafile with a different file name.

1. Connect to the database.

. oraenv

sqlplus / as sysdba

2. Check the Database Status.

select name, open_mode from v$database;

select instance_name, status from v$instance;

3. Check current datafiles.

set pages 1000 lines 1000;

column name format a70;

select FILE_ID,FILE_NAME from dba_data_files;

We take the following steps to rename a datafile.

a. Offline Tablespace

b. Move DataFile

c. Rename DataFile

d. Online Tablespace

4. Offline Tablespace

alter tablespace TEST_TBS offline;

select status from dba_tablespaces where tablespace_name = 'TEST_TBS';

Optinal steps to create tablesace:

create tablespace TEST_TBS;

CREATE TABLESPACE TEST_TBS DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/TEST_TBS_1.dbf' SIZE 10M;

5. Move DataFile

We use OS command mv to rename the physical file.

cd /u01/app/oracle/oradata/DEVDB/datafile/

mv TEST_TBS_1.dbf TEST_TBS_2.dbf

ls -ltrh

6. Rename DataFile

Inform database where is new location for the datafile

alter database rename file '/u01/app/oracle/oradata/DEVDB/datafile/TEST_TBS_1.dbf' to '/u01/app/oracle/oradata/DEVDB/datafile/TEST_TBS_2.dbf';

7. Online Tablespace

alter tablespace TEST_TBS online;

select status from dba_tablespaces where tablespace_name = 'TEST_TBS';

set pages 1000 lines 1000;

column name format a70;

select FILE_ID,FILE_NAME from dba_data_files;

Regards,

Mallik