My Oracle Support Banner

ORA-01516 When Renaming Locally-Managed TEMPFILES (Doc ID 97458.1)

Last updated on FEBRUARY 19, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 8.0.3.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

Problem Description
-------------------

Renaming a dictionary-managed file of a temporary tablespace
can be achieved by an ALTER DATABASE RENAME FILE:

   SQL> select TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT 
        from dba_tablespaces;     

   TABLESPACE_NAME CONTENTS     EXTENT_MAN
   --------------- -----------  ----------
   TEMP            TEMPORARY    DICTIONARY

   SQL> alter tablespace temp offline;
   Tablespace altered.

   SQL> alter database rename file '/V816/temp01.dbf' to '/V816/temp02.dbf';
   Database altered.

   SQL> recover tablespace temp;
   Media recovery complete.
   SQL> alter tablespace temp online;
   Tablespace altered.

whereas renaming a temporary locally-managed tempfile generates
the following errors:

   SQL> select name from v$tempfile;
   NAME
   -------------------------------------------------------------
   /V816/temp01.dbf

   SQL> alter database rename file '/V816/temp01.dbf' to '/V816/temp02.dbf';

   alter database rename file
   *ORA-01511: error in renaming log/data files
   ORA-01516: nonexistent log file, datafile or tempfile '/V816/temp01.dbf'

   SQL> alter tablespace user_temp_1 offline;
   alter tablespace user_temp_1 offline
   *ERROR at line 1:
   ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.