Oracle Segment size

Oracle Used space in Meg by segment type 

Oracle Table Size 

set pagesize 200 linesize 200 

col segment_name for a30

select * from

(

select

    owner, 

    segment_name, 

    segment_type, 

    trunc(bytes/1024/1024/1024,2) gb, 

    tablespace_name 

from  dba_segments

order by 4 desc  

) where

rownum <= 30

set pagesize 100 line 80

col "Total Used Meg"    format 999,999,990

col "Data part" format 999,999,990

col "Index part" format 999,999,990

col "LOB part" format 999,999,990

col "RBS part" format 999,999,990

tti 'Used space in Meg by segment type'

select sum(bytes)/1024/1024 "Total Used",sum( decode( substr(segment_type,1,5), 'TABLE', bytes/1024/1024, 0)) "Data  part",

sum( decode( substr(segment_type,1,5), 'INDEX', bytes/1024/1024, 0)) "Index part",

sum( decode( substr(segment_type,1,3), 'LOB', bytes/1024/1024, 0)) "LOB   part",

sum( decode(segment_type,'ROLLBACK', bytes/1024/1024, 0)) "RBS   part",

sum( decode(segment_type,'TEMPORARY', bytes/1024/1024, 0)) "TEMP  part"

from sys.dba_segments

/

tti off

tti "Total database size"

select sum(bytes)/1024/1024 "Total DB size in Meg" from sys.v_$datafile

/

tti off

set linesize 200 

select a.owner, a.table_name, trunc(b.size_gb,2) size_gb, trunc (((a.blocks*8192/1024/1024/1024)-(a.num_rows*avg_row_len/1024/1024/1024)) ,2) as actual_gb,trunc ((b.size_gb-((a.blocks*8192/1024/1024/1024)-(a.num_rows*avg_row_len/1024/1024/1024))),2) savings,

a.tablespace_name, b.segment_type table_type, a.last_analyzed from dba_tables a, (select * from   (select owner, segment_name, segment_type, tablespace_name, sum(bytes/1024/1024/1024) size_gb from  dba_segments 

where owner='&schema_name' 

-- and segment_type like '%TABLE%' 

group by segment_name, owner, segment_type, tablespace_name 

order by 5 desc) 

where rownum<11) b 

where a.table_name=b.segment_name and a.owner=b.owner

order by size_gb desc;