Oracle Temp Space Usage Related Queries

Based on Oracle 10g/11g

-- show summary temp tablespace usage
SELECT t.tablespace_name
      ,t.total_temp_mb
      ,u.used_temp_mb
      , (t.total_temp_mb - u.used_temp_mb) free_temp_mb
  FROM (SELECT   tf.tablespace_name
                ,   SUM (DECODE (tf.autoextensible
                                ,'YES', tf.maxbytes
                                ,'NO', BYTES
                                ))
                  / 1024
                  / 1024 total_temp_mb
            FROM dba_temp_files tf
        GROUP BY tf.tablespace_name) t
      , (SELECT   ss.tablespace_name
                 ,   SUM (ss.used_blocks)
                   * (SELECT ts.block_size
                        FROM dba_tablespaces ts
                       WHERE ts.CONTENTS = 'TEMPORARY'
                         AND ts.tablespace_name = ss.tablespace_name)
                   / 1024
                   / 1024 used_temp_mb
             FROM v$sort_segment ss
         GROUP BY ss.tablespace_name) u
 WHERE t.tablespace_name = u.tablespace_name

-- show temp space usage by session 
SELECT   su.TABLESPACE
        --,su.segtype
,        SUM (su.blocks * ts.block_size) / 1024 / 1024 mb
        ,se.SID
        ,se.serial#
        ,se.username
        ,se.status
        ,se.osuser
        ,se.machine
        ,se.program
        ,se.action
        ,sq.sql_text
    FROM v$sort_usage su
        ,v$session se
        ,dba_tablespaces ts
        ,v$sql sq
   WHERE su.session_addr = se.saddr
     AND ts.tablespace_name = su.TABLESPACE
     AND ts.CONTENTS = 'TEMPORARY'
     AND se.sql_address = sq.address(+)
     AND se.sql_hash_value = sq.hash_value(+)
GROUP BY su.TABLESPACE
        ,se.SID
        ,se.serial#
        ,se.username
        ,se.status
        ,se.osuser
        ,se.machine
        ,se.program
        ,se.action
        ,sq.sql_text
ORDER BY 2 DESC;


Ref:
1. Script - Temporary tablespace usage
http://gavinsoorma.com/2009/06/temp-tablespace-usage/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值