Oracle : Script to Determine Objects Per Tablespace

 

 accept tableSpaceName prompt 'Enter tablespace: '
 
 set linesize 132
 set pagesize 72
 col type format a17 heading "object type"
 col object format a111 heading "schema.Object"

 break on type skip 1 on type
 ttitle left 'Tablespace: ' &tableSpaceName - skip 1 ' '
 
 select segment_type type,
      rtrim(owner)||'.'||rtrim(segment_name) object
 from sys.dba_extents
 where file_id in (
      select file_id
      from sys.dba_data_files
      where tablespace_name = upper('&tableSpaceName'))
 group by owner, segment_name, segment_type
 order by segment_type;