表空间使用情况:
select
a.tablespace_name
,a.total_G
,b.fee_G
,(a.total_G-b.fee_G)/a.total_G as "used%"
from
(select
a.tablespace_name
,sum(a.bytes)/1024/1024/1024 As total_G
from dba_data_files a
group by
a.tablespace_name
) a
left join (select
a.tablespace_name
,sum(a.bytes)/1024/1024/1024 as fee_G
from dba_free_space a
group by
a.tablespace_name
) b
on a.tablespace_name = b.tablespace_name
order by 4 desc

临时表空间使用情况:
Select
f.tablespace_name
,sum(f.bytes_free + f.bytes_used)
/1024/1024/1024 "total GB"
,sum((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0))
/1024/1024/1024 "Free GB"
,sum(nvl(p.bytes_used, 0))
/1024/1024/1024 "Used GB"
from sys.v_$temp_space_header f, dba_temp_files d, sys.v_$temp_extent_pool p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) = d.file_id
group by
f.tablespace_name

具体到某个SID临时表空间使用情况
select
b.tablespace
,b.segfile#
,b.segblk#
,b.blocks
,b.blocks*32/1024/1024
,a.sid
,a.serial#
,a.username
,a.osuser
,a.status
,c.sql_text,b.contents
from v$session a, v$sort_usage b, v$sql c
where a.saddr = b.session_addr
and a.sql_address = c.address(+)
order by b.blocks desc