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.
I was facing the same issue and after killing the sessions using temp I was able to drop the temp files.
ReplyDelete