DBA_SEGMENTS

Storage allocated for all database segments

Columns
   ___________________________
 
   OWNER
      Username of the segment owner
   SEGMENT_NAME
      Name,if any,of the segment
   PARTITION_NAME
      Partition/Subpartition Name,if any,of the segment
   SEGMENT_TYPE
      "Type of segment: "TABLE","CLUSTER","INDEX","ROLLBACK","DEFERRED
   TABLESPACE_NAME
      Name of the tablespace containing the segment
   HEADER_FILE
      ID of the file containing the segment header
   HEADER_BLOCK
      ID of the block containing the segment header
   BYTES
      Size,in bytes of the segment
   BLOCKS
      Size,in Oracle blocks of the segment
   EXTENTS
      Number of extents allocated to the segment
   INITIAL_EXTENT
      Size,in bytes,of the initial extent of the segment
   NEXT_EXTENT
      Size,in bytes,of the next extent to be allocated to the segment
   MIN_EXTENTS
      Minimum number of extents allowed in the segment
   MAX_EXTENTS
      Maximum number of extents allowed in the segment
   PCT_INCREASE
      Percent by which to increase the size of the next extent to be allocated
   FREELISTS
      Number of process freelists allocated in this segment
   FREELIST_GROUPS
      Number of freelist groups allocated in this segment
   RELATIVE_FNO
      Relative number of the file containing the segment header
   BUFFER_POOL
      The default buffer pool to be used for segments blocks

Example sql:

-- Total size allocated for each tablespace:

COLUMN tablespace_name FORMAT A21
COLUMN Sum(bytes/(1024*1024)) FORMAT 999,999,999,999

Select tablespace_name,
Sum(bytes/(1024*1024)) TotalMB
From dba_segments
Group By tablespace_name;

-- Total space allocated by Owner:

Select owner,
sum(blocks) Totalblocks,
sum(bytes/(1024*1024)) TotalMB
From dba_segments
Group By owner

-- Total space allocated by Tablespace:

Select tablespace_name,
sum(blocks) Totalblocks,
sum(bytes/(1024*1024)) TotalMB
From dba_segments
Group By tablespace_name

-- Space used in each Segment:

SET LINESIZE 150
COLUMN tablespace_name FORMAT A15
COLUMN owner FORMAT A10
COLUMN segment_name FORMAT A35
COLUMN segment_type FORMAT A10
COLUMN extents FORMAT 9,999
COLUMN blocks FORMAT 999,999
COLUMN bytes FORMAT 999,999,999,999

Select tablespace_name,
owner,
segment_name,
segment_type, /* TABLE,INDEX */
extents, /* No. of extents in the segment*/
blocks, /* No. of db blocks in the segment*/
bytes /* No. of bytes in the segment*/
From dba_segments
Where owner NOT IN('SYSTEM','DBSNMP', 'ORDSYS', 'OUTLN','SYS')
Order By bytes ;