SELECT b.tablespace,
       ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
       a.inst_id as Instance,
       a.sid||','||a.serial# AS sid_serial,
       NVL(a.username, '(oracle)') AS username,
       a.program,
       a.status,
       a.sql_id
FROM   gv$session a,
       gv$sort_usage b,
       gv$parameter p
WHERE  p.name  = 'db_block_size'
AND    a.saddr = b.session_addr
AND    a.inst_id=b.inst_id
AND    a.inst_id=p.inst_id
ORDER BY b.tablespace, b.blocks

select * from v$temporary_lobs
order by
  cache_lobs desc 
  
  
SELECT A.inst_id,
  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 gv$sort_segment A,
  (SELECT B.INST_ID,
    B.name,
    C.block_size,
    SUM (C.bytes) / 1024 / 1024 mb_total
  FROM gv$tablespace B,
    gv$tempfile C
  WHERE B.ts#  = C.ts#
  AND c.inst_id=b.inst_id
  GROUP BY B.INST_ID,
    B.name,
    C.block_size
  ) D
WHERE A.tablespace_name = D.name
and a.tablespace_name='TEMP5M'
AND A.inst_id           =D.inst_id
GROUP BY a.inst_id,
  A.tablespace_name,
  D.mb_total
ORDER BY 1,2;