我用樓上的兩種方法都試了﹐卻發現不同的結果﹕
SQL> select sum(blocks*8192)/1024/1024 from v$sort_usage;
SUM(BLOCKS*8192)/1024/1024
--------------------------
55
SQL>
SQL> SELECT d.status "Status", d.tablespace_name "Name", d.CONTENTS "Type",
2 d.extent_management "Extent Management",
3 TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
4 TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.999')
5 || '/'
6 || TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99999999.999') "Used (M)",
7 TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0), '990.00') "Used %"
8 FROM SYS.dba_tablespaces d,
9 (SELECT tablespace_name, SUM (BYTES) BYTES
10 FROM dba_temp_files
11 GROUP BY tablespace_name) a,
12 (SELECT tablespace_name, SUM (bytes_cached) BYTES
13 FROM v$temp_extent_pool
14 GROUP BY tablespace_name) t
15 WHERE d.tablespace_name = a.tablespace_name(+)
16 AND d.tablespace_name = t.tablespace_name(+)
17 AND d.extent_management LIKE 'LOCAL'
18 AND d.CONTENTS LIKE 'TEMPORARY';
Status Name Type Extent Management Size (M) Used (M) Used %
--------- ------------------------------ --------- ----------------- --------------- --------------------------- -------
ONLINE TEMP TEMPORARY LOCAL 4,000.000 3998.000/ 4000.000 99.95
SQL>
而從V$TEMP_EXTENT_POOL中查詢到的結果剛好與在oem內看到的相同。
這里我想問的是﹐從v$sort_usage中查到的是否是當前正在使用的temp空間呢﹖從V$TEMP_EXTENT_POOL查到是否temp表空間中所有數據文件的HWM的空間之和呢﹖