Move datafile one location to different location online in oracle 12c Version 12.1.0.2 and later:
==========================================================================================================
Login as sysdba
to get the file id and datafile loation info using below query
=========================================================================
select FILE_ID ,FILE_NAME,TABLESPACE_NAME from dba_data_files ;
In OMF (Oracle Managed file name feature)
=====================================================
Error while move datafile in OMF :
SQL> ALTER DATABASE MOVE DATAFILE '/oracle/oradata/data21/abcd_grlw8cvz_.dbf' to '/oracle/oradata/data22/abcd_grlw8cvz_.dbf';
ALTER DATABASE MOVE DATAFILE '/oracle/oradata/data1/abcd_grlw8cvz_.dbf' to '/oracle/oradata/data1/abcd_grlw8cvz_.dbf'
*
ERROR at line 1:
ORA-01276: Cannot add file /oracle/oradata/data1/abcd_grlw8cvz_.dbf. File has an Oracle Managed Files file name.
SQL>
Solution
==============
Set db_create_file_dest before moving the datafile and do not specify the datafile name
SQL> alter session set db_create_file_dest='<DIR_NEW>';
SQL> alter database move datafile 21;
In Normal
==================
alter database move datafile '/u01/oradata/test/test.dbf' to '/u02/oradata/test/test.dbf';
No comments:
Post a Comment