Doc

    Name:    objstor.sql

    This script lists database object disk storage information

#

ttitle 'Object Storage Information'

   SELECT SUBSTR(s.segment_name,1,20) OBJECT_NAME, 
        SUBSTR(s.segment_type,1,5) TYPE, 
        SUBSTR(s.tablespace_name,1,10) T_SPACE,   
        NVL(NVL(t.initial_extent, i.initial_extent),r.initial_extent)/ 4096
            FST_EXT,
        NVL(NVL(t.next_extent,i.next_extent),R.NEXT_EXTENT) / 4096 NXT_EXT,
            s.extents - 1  tot_ext,
            s.blocks  tot_blks
        FROM    
            dba_rollback_segs R,
            dba_indexes I,
            dbs_tables T,
            dba_segments S
    WHERE s.segment_name     LIKE  UPPER('&S_NAME')  || '%'
      AND s.tablespace_name  LIKE  UPPER('&T_SPACE') || '%'
      AND s.extents             >  1
      AND s.owner            =     t.owner (+)
      AND s.segment_name     =     t.table_name (+)
      AND s.tablespace_name  =     t.tablespace_name (+)
      AND s.owner            =     i.owner (+)
      AND s.segment_name     =     i.index_name (+)
      AND s.tablespace_name  =     i.tablespace_name (+)
      AND s.owner            =     r.owner (+)
      AND s.segment_name     =     r.segment_name (+)
      AND s.tablespace_name  =     r.tablespace_name (+)
    ORDER BY s.segment_name,   s.segment_type;