临时表空间监控

最新推荐文章于 2021-08-20 13:29:55 发布

cq5523044

最新推荐文章于 2021-08-20 13:29:55 发布

阅读量126

点赞数

-- 参考 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;