Oracle schema size  ... 

set linesize 200 pagesize 200 timing on time on
 col Owner for a28
col "HUMAN_READABLE" for a14
col "size"      for 9999999999999
col "size GB"      for 999999.99

select obj.owner "Owner"
--, obj_cnt "Objects",decode(seg_size, NULL, 0, seg_size) "size"
,decode(seg_size, NULL, 0, seg_size)/1024/1024/1024 "size GB"
,dbms_xplan.FORMAT_SIZE(decode(seg_size, NULL, 0, seg_size)) "HUMAN_READABLE"
from ( select /*+ parallel(8) */ owner, count(*) obj_cnt from dba_objects group by owner) obj,
 ( select owner, ceil(sum(bytes)) seg_size from dba_segments group by owner) segment
where obj.owner = segment.owner(+)
order by 2 desc
--order by 3 desc, 2 desc, 1
;

DEFINE schema_name = 'XXXX'
set linesize 300

col schema_size_gb  for 9999999.99

SELECT sum(sizegb) schema_size_gb FROM (
-- tablesize !!!
--SELECT *  FROM (
SELECT
owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024/1024 AS sizegb,tablespace_name, extents, initial_extent,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_MB
FROM (
-- Tables
SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,segment_name AS table_name, bytes,tablespace_name, extents, initial_extent
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
-- Indexes
SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,i.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
--LOB Segments
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
-- LOB Indexes
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX'
)
WHERE owner in UPPER('&schema_name')
)
--WHERE total_table_MB > 10
--ORDER BY total_table_MB DESC, MB DESC
/

====

with count 

set linesize 100 pagesize 200 
col Owner for a28
select obj.owner "Owner", obj_cnt "Objects",decode(seg_size, NULL, 0, seg_size) "size GB"
from ( select owner, count(*) obj_cnt from dba_objects group by owner) obj,
     ( select owner, ceil(sum(bytes)/1024/1024/1024) seg_size from dba_segments group by owner) segment
where obj.owner = segment.owner(+)
order by 3 desc, 2 desc, 1;

col TABLE_NAME for a30
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF meg ON REPORT
select * from (
SELECT owner,
       table_name,
       trunc(SUM(bytes) / 1024 / 1024) meg
FROM   (SELECT segment_name table_name,
               owner,
               bytes
        FROM   dba_segments
        WHERE  segment_type = 'TABLE'
        UNION ALL
        SELECT i.table_name,
               i.owner,
               s.bytes
        FROM   dba_indexes  i,
               dba_segments s
        WHERE  s.segment_name = i.index_name
        AND    s.owner = i.owner
        AND    s.segment_type = 'INDEX'
        UNION ALL
        SELECT l.table_name,
               l.owner,
               s.bytes
        FROM   dba_lobs     l,
               dba_segments s
        WHERE  s.segment_name = l.segment_name
        AND    s.owner = l.owner
        AND    s.segment_type = 'LOBSEGMENT'
        UNION ALL
        SELECT l.table_name,
               l.owner,
               s.bytes
        FROM   dba_lobs     l,
               dba_segments s
        WHERE  s.segment_name = l.index_name
        AND    s.owner = l.owner
        AND    s.segment_type = 'LOBINDEX')
WHERE 1=1
-- owner NOT IN ('SYS', 'SYSTEM')
and owner  IN ('xx')
GROUP  BY table_name, owner
HAVING SUM(bytes) / 1024 / 1024 > 0 /* Ignore tables lower than 30 MB */
)
where 1=1
and meg!=0
ORDER  BY meg DESC
/