Sunday, June 15, 2014

ORA-25152: TEMPFILE cannot be dropped at this time

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orclcdb/temp02.dbf' size 10M autoextend on;

Tablespace altered.

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orclcdb/temp01.dbf';
alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orclcdb/temp01.dbf'
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

SQL> show parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200

SQL> SELECT file#, name from v$tempfile;

     FILE# NAME
---------- ----------------------------------------------------------------------
         1 /u01/app/oracle/oradata/orclcdb/temp01.dbf
         2 /u01/app/oracle/oradata/orclcdb/pdbseed/pdbseed_temp01.dbf
         3 /u01/app/oracle/oradata/orclcdb/pdb1/temp01.dbf
         4 /u01/app/oracle/oradata/orclcdb/temp02.dbf

SQL> SELECT s.sid, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocks
  2   FROM v$session s, v$sort_usage u
  3  WHERE s.saddr=u.session_addr
  4  ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;

       SID USERNAME                       STATUS   TABLESPACE   SEGFILE# CONTENTS     EXTENTS     BLOCKS
---------- ------------------------------ -------- ---------- ---------- --------- ---------- ----------
        10 SYS                            ACTIVE   TEMP              201 TEMPORARY          1        128

SQL> select sid,serial# from v$session where sid = 10;

       SID    SERIAL#
---------- ----------
        10          3

SQL> alter system kill session '10,3';

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orclcdb/temp01.dbf';

Tablespace altered.