1. Identify temporary datafile details :
SQL> select file_name,TABLESPACE_NAME from DBA_TEMP_FILES;
2. Check if there is any space available in temporary tablespace (segment)
SELECT A.tablespace_name tablespace, D.mb_total,
SUM(A.used_blocks * D.block_size)/1024/1024 mb_used
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM(C.bytes)/1024/1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
SQL> select
((blocks*8192)+8192)/1024/1024 tblks ,
s.username||' ('|| sid||','|| serial#||')' userinfo,
t.piece, t.sql_text
from v$session s, v$sort_usage u, v$sqltext t
where s.saddr=u.session_addr
and s.sql_id(+)=t.sql_id
order by 1,2,3
;
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;
3.Fix:
a) Resize temporary file as
SQL> ALTER DATABASE TEMPFILE ‘/u01/oradata/mydb/temp01.dbf’ RESIZE 3072M;
or
b) Add temp datafile to temporary tablespace as
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oradata/mydb/temp02.dbf’
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 1024M;
Root Cause Analysis
1. Identify temp segment usages:
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND (b.blocks*d.block_size)/1048576 > 1024
ORDER BY b.tablespace, 6 desc;
Get 10 sessions with largest temp usage
cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;
