查询Oracle 临时表空间使用情况

最新推荐文章于 2024-11-04 14:28:31 发布

原创最新推荐文章于 2024-11-04 14:28:31 发布 · 938 阅读

0 ·

CC 4.0 BY-SA版权

scripts 专栏收录该内容

2 篇文章

订阅专栏

本文深入探讨了SQL查询中临时表空间的管理与使用情况，通过展示具体SQL语句及其输出，揭示了如何优化查询性能及分析排序操作的资源消耗。

SELECT d.tablespace_name “Name”,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),’99,999,990.900’) “Size (M)”,
TO_CHAR(NVL(t.hwm, 0)/1024/1024,’99999999.999’) “HWM (M)”,
TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), ‘990.00’) “HWM % ” ,
TO_CHAR(NVL(t.bytes/1024/1024, 0),’99999999.999’) “Using (M)”,
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), ‘990.00’) “Using %”
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like ‘LOCAL’
AND d.contents like ‘TEMPORARY’
/

select * from v$sort_usage

select TABLESPACE_NAME, BYTES_USED/1024/1024 used_MB,BYTES_FREE/1024/1024 free_MB
from V$TEMP_SPACE_HEADER;