SQL/Plus

Check the usage of the temporary tablespaces

show_tmp_usage_by_tbs.sql:

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
------------------------------- ---------- ---------- ----------
TMP_CDB                                500          3        497
TMP_STDB                              1000          0       1000
TEMP                                    75          0         75