本文共 1891 字，大约阅读时间需要 6 分钟。

Based on Oracle 10g/11g

-- show summary temp tablespace usageSELECT 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_textORDER BY 2 DESC;

Ref:

转载地址：http://ajtai.baihongyu.com/