Oracle Temp Tablespace info

Oracle Temp Tablespace info
Oracle Temp Tablespace

Oracle Temp File usage Info  ...

set lines 300
COLUMN mb_total        HEADING 'Total|MB'        FORMAT 99,999
COLUMN mb_used         HEADING 'Used|MB'         FORMAT 99,999
COLUMN mb_free         HEADING 'Free|MB'         FORMAT 99,999
column FILE_NAME       Heading 'File|Name'       FORMAT a30
column tablespace_name Heading 'Tablespace|Name' FORMAT a10
column TABLESPACE      Heading 'Tablespace|Name' FORMAT a10
column MB              Heading 'Allocated|MB'    FORMAT 99,999
SELECT dbatf.FILE_NAME, dbatf.BYTES/1024/1024 MB, 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 dba_temp_files dbatf, 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, dbatf.FILE_NAME, dbatf.BYTES/1024/1024
/



File                           Allocated Tablespace   Total    Used    Free
Name                                  MB Name            MB      MB      MB
------------------------------ --------- ---------- ------- ------- -------
+DATA/orcl/temp01.dbf                 29 TEMP            29       0      29