SIZE

Database Size
-------------

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select    round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,    round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
    round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,    round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select    bytes
    from    v$datafile
    union    all
    select    bytes
    from     v$tempfile
    union     all
    select     bytes
    from     gv$log) used
,    (select sum(bytes) as p
    from dba_free_space) free
group by free.p
/

Schema Size
-----------
select owner,sum(bytes)/1024/1024/1024 "SIZE in GB" from dba_segments where owner='&owner';

Table Size
----------
select sum(bytes/1024/1024) “size in MB” from dba_segments where segment_name=’&TABLE_NAME’ and segment_type=’TABLE’;

Schemas size in a tablespace
----------------------------
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select    obj.owner "Owner"
,    obj_cnt "Objects"
,    decode(seg_size, NULL, 0, seg_size) "size MB"
from     (select owner, count(*) obj_cnt from dba_objects group by owner) obj
,    (select owner, ceil(sum(bytes)/1024/1024) seg_size
    from dba_segments group by owner) seg
where     obj.owner  = seg.owner(+)
order    by 3 desc ,2 desc, 1
/

All tables owned by a user
--------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select    owner
,       tablespace_name
,       segment_name
,       segment_type
,       PARTITION_NAME
,    ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from    dba_segments
where    owner like '&user'
and    segment_type = 'TABLE'
group    by segment_name
order     by ceil(sum(bytes) / 1024 / 1024) desc
/