This is a script that will give you the sizes of the objects in a particular schema.
#!/bin/ksh
############################################################################
# Script Name..: 990_obj_size_schema.ksh
# Description..: Object Size Schema
# Author.......: Dave Venus / Michael Culp
# Date.........: 03/16/2012
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
spool logs/990_obj_size_schema.log
set serveroutput on size 1000000
SET PAGESIZE 128
SET lines 128
col num_rows format 999,999,999,990 just center heading 'Row Cnt'
col size format a25 just center heading 'Disk Size'
col num_exts format 999,999,999,990 just center heading 'Extent Cnt'
col table_name heading 'Table Name'
break on report
compute sum label total of size on report
Select a.table_name,
b.segment_type,
a.num_rows,
Case
when sum(b.bytes) < 1024 then to_char(sum(b.bytes), '999,999,999,990.00') || ' B '
when sum(b.bytes) between 1024 and (1024*1024) then to_char(sum(b.bytes)/1024, '999,999,999,990.00') || ' KB'
when sum(b.bytes)between (1024*1024) and (1024*1024*1024) then to_char(sum(b.bytes)/(1024*1024), '999,999,999,990.00') || ' MB'
when sum(b.bytes)> (1024*1024*1024) then to_char(sum(b.bytes)/(1024*1024*1024), '999,999,999,990.00') || ' GB'
end "SIZE",
sum(b.extents) num_exts
from sys.DBA_TABLES a,
sys.dba_segments b
where a.owner = 'SCHEMA_OWNER' and
b.owner = a.owner and
b.segment_name = a.table_name and
B.SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
group by a.table_name, b.segment_type, a.num_rows
order by a.table_name, b.segment_type;
spool off
exit;
EOF
#!/bin/ksh
############################################################################
# Script Name..: 991_obj_size_schema.ksh
# Description..: Object Size Schema
# Author.......: Michael Culp
# Date.........: 03/16/2012
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
spool logs/991_obj_size_schema.log
set serveroutput on size 1000000
SET PAGESIZE 128
SET lines 128
col num_rows format 999,999,999,990 just center heading 'Row Cnt'
col size format 999,999,999.99 just center heading 'Disk Size'
col num_exts format 999,999,999,990 just center heading 'Extent Cnt'
col table_name heading 'Table Name'
break on report
compute sum label total of size on report
SELECT idx.index_name,
SUM(bytes)/1024/1024/1024 as "Size"
FROM dba_segments seg,
dba_indexes idx
WHERE idx.table_owner = 'SCHEMA_OWNER'
AND idx.owner = seg.owner
AND idx.index_name = seg.segment_name
GROUP BY idx.index_name;
spool off
exit;
EOF