How to delete tempfile in temporary tablespace in Oracle database

First identify the temporary tablespace and corresponding datafiles issueing following command.



SQL> select d.bytes/1024/1024/1024,d.* from dba_temp_files d


Now take the datafile [which you want to delete] offline.
alter database tempfile '/D03/DUMMY/db/apps_st/data/temp2.dbf' offline;

Now try to delete the datafile issueing following command.
SQL> alter database tempfile '/D03/DUMMY/db/apps_st/data/temp2.dbf'  DROP INCLUDING DATAFILES;

But if it throws the following error, that means some session is still using the datafile.

*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time


To resolve the above error, first identify the seesions using the following query.

SELECT a.INST_ID,b.TABLESPACE
, b.segfile#
, b.segblk#
, ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
, a.SID
, a.serial#
, a.username
, a.osuser
, a.program
, a.status
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr
AND a.paddr = c.addr
ORDER BY b.TABLESPACE
, b.segfile#
, b.segblk#
, b.blocks;

Then kill the respective sessions using the following type of command.
alter system kill session '331,8' IMMEDIATE;


Then again issue the following command to remove the oracle temporary tablespace datafile.
alter database tempfile '/D03/DUMMY/db/apps_st/data/temp2.dbf'  DROP INCLUDING DATAFILES;