Scripts for Temp Space Checking and Troubleshooting

“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;

------------------------------ ---------- ------------ ----------
TMP_TBS                                52           52         52
TEMPJOB                               208          208        207

Anybody using more than 70% of a TBS?

select sid,
username, "%Used",TABLESPACE
  from (select s.inst_id,
               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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s