ITPub博客

首页 > 数据库 > Oracle > 临时表空间监控

临时表空间监控

原创 Oracle 作者:csbin 时间:2014-02-11 11:51:52 0 删除 编辑

-- 参考 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-01-12

  • 博文量
    69
  • 访问量
    1461368