select tablespace_name,file_name,bytes/1024/1024 "file_size(M)",autoextensible from dba_temp_files;

 select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

Select se.username, 

         se.sid,

         su.extents,

         su.blocks * to_number(rtrim(p.value)) as Space,

         tablespace,

         segtype,

         sql_text

    from v$sort_usage su, v$parameter p, v$session se, v$sql s

   where p.name = 'db_block_size'

     and su.session_addr = se.saddr

     and s.hash_value = su.sqlhash

     and s.address = su.sqladdr

   order by se.username, se.sid

select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text

from v$sort_usage sort, v$session sess, v$sql sql

where sort.SESSION_ADDR = sess.SADDR

and sql.ADDRESS = sess.SQL_ADDRESS

order by blocks desc;

select * from dba_tablespace_groups;

这几句供参考

select d.tablespace_name,
space "sum_space(m)",
blocks sum_blocks,
used_space "used_space(m)",
round(nvl(used_space, 0) / space * 100, 2) "used_rate(%)",
nvl(free_space, 0) "free_space(m)"
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) space,
sum(blocks) blocks
from dba_temp_files
group by tablespace_name) d,
(select tablespace_name,
round(sum(bytes_used) / (1024 * 1024), 2) used_space,
round(sum(bytes_free) / (1024 * 1024), 2) free_space
from v$temp_space_header
group by tablespace_name) f
where d.tablespace_name = f.tablespace_name(+);

------------------------------------------------------------

select tablespace_name,sum(bytes/1024/1024) M from dba_data_files group by tablespace_name;