Who is using TEMP tablespace?

SQL Running out of Temporary tablespace

TEMP tablespace currently using


1.

set linesize 200
col sid format a12
col sql_text format a50
select s.sid || ',' || s.serial# sid,s.username,u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb,s.sql_id
from v$sort_usage u, v$session s, v$sqlarea a,v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
group by s.sid || ',' || s.serial#, s.username,
substr(a.sql_text, 1, (instr (a.sql_text, ' ')-1)),u.tablespace,round(((u.blocks*p.value)/1024/1024),2),s.sql_id;



2

select
s.INST_ID node,
segfile#,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb,s.sql_id
from
gv$sort_usage u,
gv$session s,
gv$process p,
v$parameter par
where
s.inst_id=u.inst_id AND
s.saddr = u.session_addr AND
s.paddr = p.addr AND
par.name='db_block_size'
group by
s.INST_ID,
segfile#,s.sql_id
order by 1,2
;


3.

set linesize 140
set pagesize 400
set head on
col iid for 990
col sid_serial for a11
column username format a10
col module for a13
col action for a15
col state for a10
column segfile# format 9,999 heading 'File|ID'
col orapid for 999999 Heading "orapid||Oracle|pid"
col dbpid for a6 HEADING "spid|Unix|pid"
col apppid for a10;
column program format a55
column segblk# format 999,999,999 heading 'Block|ID'

set timi off;
CLEAR COMPUTES;
break on iid

select distinct
s.INST_ID iid,
lpad(s.sid,5)||','|| Lpad(s.serial#,5) sid_serial,
p.pid orapid,
p.spid dbpid,
s.USERNAME,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb,
s.status,
s.state,
u.SQL_ID,
segfile#,
s.module,
s.action
from
gv$sort_usage u,
gv$session s,
gv$process p,
v$parameter par
where
s.inst_id=u.inst_id AND
s.saddr = u.session_addr AND
s.paddr = p.addr AND
par.name='db_block_size'
group by
s.INST_ID,
lpad(s.sid,5)||','|| Lpad(s.serial#,5),
p.pid,
p.spid,
s.USERNAME,
s.status,
s.state,
u.SQL_ID,
segfile#,
s.module,
s.action
order by 1,2
;



4

SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

5.


PROMPT consuption by instances, tempfiles:

select
s.INST_ID iid,
segfile#,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb
from
gv$sort_usage u,
gv$session s,
gv$process p,
v$parameter par
where
s.inst_id=u.inst_id AND
s.saddr = u.session_addr AND
s.paddr = p.addr AND
par.name='db_block_size'
group by
s.INST_ID,
segfile#
order by 1,2
;