Dba Segments

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 ;

DBA_FREE_SPACE

Free extents in all tablespaces

Columns
   ___________________________

   TABLESPACE_NAME
      Name of the tablespace containing the extent
   FILE_ID
      ID number of the file containing the extent
   BLOCK_ID
      Starting block number of the extent
   BYTES
      Size of the extent in bytes
   BLOCKS
      Size of the extent in ORACLE blocks
   RELATIVE_FNO
      Relative number of the file containing the extent
Example

--Free space in each tablespace:

COLUMN tablespace_name FORMAT A15
COLUMN "Total Free (MB) " FORMAT 999,999,999,999
COLUMN "Largest Free Extent (MB) " FORMAT 999,999,999,999

Select tablespace_name,
Sum(bytes/(1024*1024)) "Total Free (MB) ",
Max(bytes/(1024*1024)) "Largest Free Extent (MB) "
From dba_free_space
Group By tablespace_name;


DBA_DATA_FILES

Information about database data files

Columns
   ___________________________

   FILE_NAME
      Name of the database data file
   FILE_ID
      ID of the database data file
   TABLESPACE_NAME
      Name of the tablespace to which the file belongs
   BYTES
      Size of the file in bytes
   BLOCKS
      Size of the file in ORACLE blocks
   STATUS
      File status: "INVALID" or "AVAILABLE"
   RELATIVE_FNO
      Tablespace-relative file number
   AUTOEXTENSIBLE
      Autoextensible indicator: "YES" or "NO"
   MAXBYTES
      Maximum size of the file in bytes
   MAXBLOCKS
      Maximum size of the file in ORACLE blocks
   INCREMENT_BY
      Default increment for autoextension
   USER_BYTES
      Size of the useful portion of file in bytes
   USER_BLOCKS
      Size of the useful portion of file in ORACLE blocks
Example SQL

-- List the Files in each Tablespace:

Select
RPAD(tablespace_name,18)||' '||RTRIM(file_name) Tablespace
From
dba_data_files
Order By tablespace_name;

To check for particular owner and table
===========================
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 IN('XXPRD')
--and SEGMENT_NAME like '%BKP%'
Order By bytes ;

Ref:
http://ss64.com/orad/DBA_SEGMENTS.html