Step-1
Run @temp_use.sql as following. Following is the code for temp_use.sql
SELECT b.TABLESPACE,
b.segfile#,
b.segblk#,
b.blocks,
a.SID,
a.serial#,
a.username,
a.osuser,
a.status,
c.sql_text
FROM v$session a,
v$sort_usage b,
v$sqlarea c
WHERE a.saddr = b.session_addr
AND a.SQL_HASH_VALUE = c.HASH_VALUE
and a.sid not in (select distinct sid from v$mystat)
ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks
/
set pagesize 10000
set linesize 133
column tablespace format a15 heading ‘Tablespace Name’
column segfile# format 9,999 heading ‘File|ID’
column segblk# format 999,999,999 heading ‘Block|ID’
column blocks format 999,999,999 heading ‘Blocks’
column username format a15
select
b.tablespace, b.segfile#, b.segblk#, b.blocks,
a.sid, a.serial#, a.username, a.osuser, a.status
from v$session a, v$sort_usage b
where a.saddr = b.session_addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
Step-2
If no transcation is active means if no rows selected from both of the queries then do the Following
alter tablespace TEMP offline;
alter tablespace TEMP online;
alter tablespace TEMP coalesce;