通过DBA_TEMP_FREE_SPACE视图查看临时表空间使用状况

通过DBA_TEMP_FREE_SPACE是oracle视图能查看临时表空间使用状况，通过FREE_SPACE字段能够很明显看到临时表空间剩余多少。

SELECT TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024 AS TABLESPACE_SIZE_G,ALLOCATED_SPACE/1024/1024/1024 AS ALLOCATED_SPACE_G,FREE_SPACE/1024/1024/1024 AS FREE_SPACE_G
 FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME            TABLESPACE_SIZE_G ALLOCATED_SPACE_G FREE_SPACE_G------------------------------ ----------------- ----------------- ------------TEMP                      1.92773438    1.92773438   1.92675781

也可以用其他语句查到临时表空间使用状况

SELECT   A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
(
SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM     v$tablespace B, v$tempfile C
WHERE    B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

TABLESPACE           MB_TOTAL    MB_USED    MB_FREE
-------------------- ---------- ---------- ----------
TEMP               1974      0     1974