“Holy Temp Usage, DBAtman!!”
What do we do?
There are some Scripts for Temp Space Checking and Troubleshooting:
The classing and his output:
select TABLESPACE_NAME, round(TABLESPACE_SIZE/1024/1024/1024) SIZE_GB, round(ALLOCATED_SPACE/1024/1024/1024) ALLOCATED_GB, round(FREE_SPACE/1024/1024/1024) FREE_GB from dba_temp_free_space; TABLESPACE_NAME SIZE_GB ALLOCATED_GB FREE_GB ------------------------------ ---------- ------------ ---------- TMP_TBS 52 52 52 TEMPJOB 208 208 207
Anybody using more than 70% of a TBS?
select sid,
inst_id,
username, "%Used",TABLESPACE
from (select s.inst_id,
s.username,
s.SQL_ADDRESS,
s.SID,
TABLESPACE,
sum(blocks * blk_size) / 1024 / 1024 Size_Mb,
trunc((sum(blocks * tf.blk_size) / max(tf.sz)) * 100, 2) "%Used"
from gv$sort_usage SU,
gv$session s,
(select tablespace_name,
max(bytes / blocks) blk_size,
sum(bytes) sz
from dba_temp_files
group by tablespace_name) tf
where su.inst_id = s.inst_id(+)
and su.SESSION_ADDR = s.SADDR(+)
and su.TABLESPACE(+) = tf.tablespace_name
and TABLESPACE = nvl(upper('&TBS'), TABLESPACE)
group by s.inst_id, s.username, s.SQL_ADDRESS, s.SID, TABLESPACE
order by sum(blocks * blk_size) / 1024 / 1024)
where "%Used"> 70
order by 4 desc;
Here is another one to do the same, but based on used size:
col sid format 99999
col tbs format a20
SELECT /*+ rule*/
s.sql_id, s.sid, s.username, u.tablespace TBS, u.segtype, u.extents,
(u.blocks * (select value from v$parameter where name = 'db_block_size')) /1024/1024 MB_used,
(select sum(f.bytes) from dba_temp_files f where f.tablespace_name = u.tablespace) /1024/1024 Total_temp_mb
FROM v$sort_usage u, v$session s
WHERE s.saddr = u.session_addr
and ((u.blocks * (select value from v$parameter where name = 'db_block_size')) /1024/1024) > 1000
order by S.sid
order by 6 asc
Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!
dbatman.blog 