Größe des Temp-Tablespaces anzeigen

Lässt man sich die “normalen” Tablespace anzeigen, so fehlt grundsätzlich der Tablespace “Temp”. Folgendes Skript zeigt Temp an.

sqlplus / as sysdba

--SQL
SET PAGESIZE 60
SET LINESIZE 300
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
;