SELECT
 CAST(dtf.tablespace_name AS VARCHAR2(6)) tablespace,
 CAST(dtf.file_name AS VARCHAR2(45))      file_name,
 dtf.file_id                        file_id,
 NVL(ROUND(tep.bytes_cached/1024/1024,2), 0) ts_used,
 ROUND(dtf.bytes/1024/1024,2)                size_mb,
 TRUNC((tep.bytes_cached / dtf.bytes)*100)   pct_used 
FROM  sys.dba_temp_files dtf,
 v$temp_extent_pool tep,
 v$tempfile tf
WHERE tep.file_id (+) = dtf.file_id
AND  dtf.file_id = tf.file#
ORDER BY dtf.file_id
;

-- Oracle Database Temp Space free and Usage (%)

SELECT  TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss') as_of,
  SUBSTR(ss.tablespace_name,1,15) tablespace,
  q_tmp.mb_total,
  SUM(ss.used_blocks * q_tmp.block_size)/1024/1024 mb_used,
  q_tmp.mb_total - SUM(ss.used_blocks * q_tmp.block_size)/1024/1024 mb_free, 
  100*(SUM(ss.used_blocks * q_tmp.block_size)/1024/1024/q_tmp.mb_total)
  "% Used"
FROM  v$sort_segment ss,
    (SELECT   ts.name, tf.block_size, SUM(tf.bytes)/1024/1024 mb_total
     FROM     v$tablespace ts, v$tempfile tf
     WHERE    ts.ts#= tf.ts#
     GROUP BY ts.name, tf.block_size
    ) q_tmp
WHERE    ss.tablespace_name = q_tmp.name
GROUP by SUBSTR(ss.tablespace_name,1,15), q_tmp.mb_total
;