ITRemote

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