HOW TO RENAME OR RELOCATE DATAFILE( non-system datafile)

STEPS TO RENAME OR RELOCATE DATAFILE(non-system datafile)

       

1.Determine in what tablespace this datafile is

2.Take tablespace offline for which we need to relocate datafile.

3.Move datafile to new location at the OS level.

4.Rename datafile at database level.

5.Take tablespace online.

6.Check tablespace detail.

STEP 1: CHECK IN WHAT TABLESPACE THIS DATAFILE IS

                SQL> select file_name,tablespace_name,status from dba_data_files;

                FILE_NAME                                                               TABLESPACE            STATUS

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

/u02/app/oracle/oradata/DEV/users01.dbf                 USERS                  AVAILABLE

/u02/app/oracle/oradata/DEV/undotbs01.dbf           UNDOTBS1        AVAILABLE

/u02/app/oracle/oradata/DEV/sysaux01.dbf              SYSAUX              AVAILABLE

/u02/app/oracle/oradata/DEV/system01.dbf             SYSTEM              AVAILABLE

/u02/app/oracle/oradata/DEV/testtbs.dbf                  TEST_TBS          AVAILABLE

STEP 2: TAKE TABLESPACE OFFLINE

SQL> alter tablespace TEST_TBS offline;

Tablespace altered.

STEP 3: MOVE DATAFILE TO NEW LOCATION AS OS LEVEL.

[oracle@dev DEV]$ mv /u02/app/oracle/oradata/DEV/testtbs.db  /u01/datafile/

STEP4: RENAME DATAFILE AT DATABASE LEVEL.

alter tablespace TEST_TBS rename datafile '/u02/app/oracle/oradata/DEV/testtbs.dbf' to '/u01/datafile/testtbs.dbf';

Tablespace altered.

STEP5: TAKE TABLESPACE ONLINE

SQL> alter tablespace TEST_TBS online;

Tablespace altered.

Step 6: CHECK TABLESPACE DETAIL.

                SQL> select tablespace_name, status from dba_tablespaces ;

TABLESPACE_NAME                STATUS

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

SYSTEM                        ONLINE

SYSAUX                        ONLINE

UNDOTBS1                  ONLINE

TEMP                            ONLINE

USERS                           ONLINE

TEST_TBS                     ONLINE

6 rows selected.
 

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!