1
2
3
4
5
6
7
8

select * from (
  select t.tablespace_name,t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb
  from dba_segments t
  where t.segment_type='TABLE'
	AND tablespace_name ='TEMPTABS'
  group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type
) t
order by t.mb desc;

1
2
3
4
5
6
7
8
9

select t.tablespace_name,
       d.file_name,
       d.bytes / 1024 / 1024 as size_mb,
       d.autoextensible,
       d.maxbytes / 1024 / 1024 as max_size_mb
  from dba_tablespaces t
  join dba_data_files d
    on t.tablespace_name = d.tablespace_name
 order by t.tablespace_name, d.file_name;