语句1:
SELECT a.tablespace_name, a.BYTES total, a.bytes - nvl(b.bytes, 0) free,nvl(b.bytes, 0)/a.BYTES
FROM (SELECT tablespace_name, SUM (bytes) bytes FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (bytes_cached) bytes FROM v$temp_extent_pool GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
查询结果100%语句2:
SELECT d.tablespace_name tablespace_name,
NVL(SUM(used_blocks), 0) * 8 / 1024 used_M,
SUM(blocks) * 8 / 1024 total_m,
NVL(SUM(used_blocks), 0) * 100 / SUM(blocks) used_percent
FROM v$sort_segment v, dba_temp_files d
WHERE d.tablespace_name = v.tablespace_name(+)
GROUP BY d.tablespace_name
ORDER BY used_percent DESC;查询结果0
不解:第一个语句是网上dowm的,第二个语句是DBA给的;用ORACLE客户单看利用率是100%,用Toad看利用率是0。
这两个语句查询的针对主体不同吗。纠结
SELECT a.tablespace_name, a.BYTES total, a.bytes - nvl(b.bytes, 0) free,nvl(b.bytes, 0)/a.BYTES
FROM (SELECT tablespace_name, SUM (bytes) bytes FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (bytes_cached) bytes FROM v$temp_extent_pool GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
查询结果100%语句2:
SELECT d.tablespace_name tablespace_name,
NVL(SUM(used_blocks), 0) * 8 / 1024 used_M,
SUM(blocks) * 8 / 1024 total_m,
NVL(SUM(used_blocks), 0) * 100 / SUM(blocks) used_percent
FROM v$sort_segment v, dba_temp_files d
WHERE d.tablespace_name = v.tablespace_name(+)
GROUP BY d.tablespace_name
ORDER BY used_percent DESC;查询结果0
不解:第一个语句是网上dowm的,第二个语句是DBA给的;用ORACLE客户单看利用率是100%,用Toad看利用率是0。
这两个语句查询的针对主体不同吗。纠结
抛开语句,用客户端看,ORACLE里显示100%,TOAD里显示0。
而后一条语句( v$sort_segment) 用于显示被排序操作所使用的临时表空间的大小。
from V$TEMP_SPACE_HEADER;