VIEW: SYS.DBA_TEMP_FREE_SPACE

Source

View Text - Preformatted

SELECT tsh.tablespace_name,
         tsh.total_bytes/tsh.inst_count,
         tsh.bytes_used/tsh.inst_count,
         (tsh.bytes_free/tsh.inst_count) + (nvl(ss.free_blocks, 0) * ts$.blocksize)
    FROM (SELECT tablespace_name, sum(bytes_used + bytes_free) total_bytes,
                 sum(bytes_used) bytes_used, sum(bytes_free) bytes_free,
                 count(distinct inst_id) inst_count
            FROM gv$temp_space_header
            where (con_id is NULL or con_id = sys_context('USERENV', 'CON_ID'))
            GROUP BY tablespace_name) tsh,
         (SELECT tablespace_name, sum(free_blocks) free_blocks
            FROM gv$sort_segment
            where (con_id is NULL or con_id = sys_context('USERENV', 'CON_ID'))
            GROUP BY tablespace_name) ss,
         ts$
    WHERE ts$.name = tsh.tablespace_name and
          tsh.tablespace_name = ss.tablespace_name (+)

View Text - HTML Formatted

SELECT TSH.TABLESPACE_NAME
, TSH.TOTAL_BYTES/TSH.INST_COUNT
, TSH.BYTES_USED/TSH.INST_COUNT
, (TSH.BYTES_FREE/TSH.INST_COUNT) + (NVL(SS.FREE_BLOCKS
, 0) * TS$.BLOCKSIZE)
FROM (SELECT TABLESPACE_NAME
, SUM(BYTES_USED + BYTES_FREE) TOTAL_BYTES
, SUM(BYTES_USED) BYTES_USED
, SUM(BYTES_FREE) BYTES_FREE
, COUNT(DISTINCT INST_ID) INST_COUNT
FROM GV$TEMP_SPACE_HEADER
WHERE (CON_ID IS NULL OR CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID')) GROUP BY TABLESPACE_NAME) TSH
, (SELECT TABLESPACE_NAME
, SUM(FREE_BLOCKS) FREE_BLOCKS
FROM GV$SORT_SEGMENT
WHERE (CON_ID IS NULL OR CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID')) GROUP BY TABLESPACE_NAME) SS
, TS$
WHERE TS$.NAME = TSH.TABLESPACE_NAME AND TSH.TABLESPACE_NAME = SS.TABLESPACE_NAME (+)