APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
On : 12.2.0.1 version, RDBMShttp://www.cndba.cn/hbhe0316/article/108528

Moving a temp datafile ONLINE using below command :http://www.cndba.cn/hbhe0316/article/108528

SQL> ALTER DATABASE MOVE DATAFILE 'D:/LOCATION/temp01.dbf' TO 'F:/LOCATION/temp01.dbf';



Got below error :
ALTER DATABASE MOVE DATAFILE 'D:/LOCATION/temp01.dbf' TO ''F:/LOCATION/temp01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"D:/LOCATION/temp01.dbf" in the current container

CAUSE
“alter database move” only applicable for non TEMP datafilehttp://www.cndba.cn/hbhe0316/article/108528http://www.cndba.cn/hbhe0316/article/108528

Reproduced the issue in Labr :http://www.cndba.cn/hbhe0316/article/108528http://www.cndba.cn/hbhe0316/article/108528

SQL> alter database move tempfile '/LOCATION/temp1.dbf' to '/LOCATION/oradata/temp1.dbf';

Got Similar ERROR:

 alter database move tempfile '/LOCATION/temp1.dbf' to '/LOCATION/temp1.dbf'
  *
ERROR at line 1:
ORA-00905: missing keyword

SOLUTION
Since this is for TEMPORARY tablespace, below steps can be done while database is running:http://www.cndba.cn/hbhe0316/article/108528

1. create new Temporary tablespace for ex: TEMP2
2. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2
3. drop OLD temp tablespace

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle