临时表空间监控

-- 参考 http://hi.baidu.com/whxaszxcv/item/54424a0cc31bfce6f55ba6df
----------------------------------------------------------------------------
-- 查看当前用户的临时表空间
----------------------------------------------------------------------------
select *
  from database_properties
 where property_name = 'DEFAULT_TEMP_TABLESPACE';
----------------------------------------------------------------------------
-- 查看表空间占用情况
----------------------------------------------------------------------------
SELECT a.tablespace_name,
       a.bytes total,
       nvl(b.bytes_used, 0) bytes_used,
       nvl(b.bytes_cached, 0) bytes_cached
  FROM (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_temp_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name,
               SUM(bytes_used) bytes_used,
               SUM(bytes_used) bytes_cached
          FROM v$temp_extent_pool
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+);

select tablespace_name, bytes_used, bytes_cached from v$temp_extent_pool t;

select t.added_extents from v$sort_segment t;

select * from v$sort_usage;
----------------------------------------------------------------------------
-- 哪些用户和SQL占用临时表空间（临时表和排序操作）
----------------------------------------------------------------------------
select distinct a.sid,
                a.process,
                a.serial#,
                to_char(a.logon_time, 'yyyy-mm-dd hh24:mi:ss'),
                a.osuser,
                tablespace,
                b.sql_text
  from v$session a, v$sql b, v$sort_usage c
 where a.sql_address = b.address(+)
   and a.saddr = c.session_addr;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/720091/viewspace-1079405/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/720091/viewspace-1079405/