Skip to content

Alter Database Move Tempfile?

  • by

Move Temp File Online?

We have talked about moving a data file online in the previous post and we're thinking about moving a temp file in the same way.

Let's see the test.

SQL> alter database move tempfile '/oradata/ORCLCDB/ORCLPDB/temp02.dbf' to '/oradata/TESTCDB/ORCLPDB/temp02.dbf' reuse;
alter database move tempfile '/oradata/ORCLCDB/ORCLPDB/temp02.dbf' to '/oradata/TESTCDB/ORCLPDB/temp02.dbf' reuse
                    *
ERROR at line 1:
ORA-00905: missing keyword

ORA-00905 was thrown. This is simply because Oracle doesn't support ALTER DATABASE MOVE TEMPFILE, only ALTER DATABASE MOVE DATAFILE statement is available for DBA to use in Oracle.

Let's pretend it's a data file, then we do it again.

SQL> alter database move datafile '/oradata/ORCLCDB/ORCLPDB/temp02.dbf' to '/oradata/TESTCDB/ORCLPDB/temp02.dbf' reuse;
alter database move datafile '/oradata/ORCLCDB/ORCLPDB/temp02.dbf' to '/oradata/TESTCDB/ORCLPDB/temp02.dbf' reuse
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/oradata/ORCLCDB/ORCLPDB/temp02.dbf" in the current container

It's ORA-01516 this time. Apparently, we can't move temp files in this way.

To achieve the goal, we have some ways to move a temp file from one place to another.

Leave a Reply

Your email address will not be published. Required fields are marked *