find oracle temp tablespace usage in DB - Oracle ##

 find oracle temp tablespace usage in DB - Oracle ##

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

 

SELECT 

   A.tablespace_name tablespace, 

   D.mb_total,

   SUM (A.used_blocks * D.block_size) /1024/1024/1024 gb_used,

   D.mb_total - SUM (A.used_blocks * D.block_size) /1024/1024/1024 gb_free

FROM 

   v$sort_segment A,

(

SELECT 

   B.name, 

   C.block_size, 

   SUM (C.bytes) /1024/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

/