Moving DataFile to Another Location ORACLE

alter-databasedatafileoracle-12c

I'm having some problems moving DATAFILE from one location to another due to the shortage of storage space. I've managed to change the path file of control file via command(linux). Now I would like to change the path file of the datafile and logs to another since I've already manual moved the folder of the database.

 SQL> startup;
 ORACLE instance started.

 Total System Global Area 6710886400 bytes
 Fixed Size                  2939560 bytes
 Variable Size            3506439512 bytes
 Database Buffers         3187671040 bytes
 Redo Buffers               13836288 bytes
 Database mounted.
 ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
 ORA-01110: data file 1: '/app/oracle/oradata/ENOVIA/system01.dbf'

Apparently, the data files have already been moved to another location, whereas the path of the files has yet to be changed.

How can I check for the current location of Database in SQL and which command is needed to be used to alter the path file?

The new location is home/oradata/ENOVIA.

Best Answer

The SQL statement required to list the database files locations would be something like:

set lines 230
set pages 50
column file_name format a50
select ddf.FILE_NAME, ddf.TABLESPACE_NAME, ddf.STATUS, ddf.ONLINE_STATUS from dba_data_files ddf;

This will return something like the following:

|              FILE_NAME                  | TABLESPACE_NAME |  STATUS   | ONLINE_STATUS |
|-----------------------------------------|-----------------|-----------|---------------|
| /app/oracle/oradata/ENOVIA/SYSTEM01.DBF | SYSTEM          | AVAILABLE | SYSTEM        |
| /app/oracle/oradata/ENOVIA/SYSAUX01.DBF | SYSAUX          | AVAILABLE | ONLINE        |
| /app/oracle/oradata/ENOVIA/UNDOTS01.DBF | UNDOTS          | AVAILABLE | ONLINE        |
| /app/oracle/oradata/ENOVIA/USERS01.DBF  | USERS           | AVAILABLE | ONLINE        |

Once you have found the contradicting entry, you can modify it with the following SQL statement:

ALTER DATABASE 
    RENAME FILE '/app/oracle/oradata/ENOVIA/system01.dbf' 
             TO '/home/oradata/ENOVIA/system01.dbf'

Reference: Renaming and Relocating Datafiles (Oracle | Docs)

Note:

To rename or relocate datafiles of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.

Good luck.

Related Question