Oracle Top TABLES BY Size including lob, indexes

 

WITH

    table_segs AS (

        SELECT owner, segment_name AS table_name, SUM(bytes) AS bytes

        FROM dba_segments

        WHERE segment_type = 'TABLE'

        GROUP BY owner, segment_name

    ) /* ,

    table_part_segs AS (

        SELECT p.table_owner AS owner, p.table_name, SUM(s.bytes) AS bytes

        FROM dba_segments s

        JOIN dba_tab_partitions p           ON s.owner = p.table_owner AND s.segment_name = p.partition_name

        WHERE s.segment_type = 'TABLE PARTITION'

        GROUP BY p.table_owner, p.table_name

    ) ,

    index_segs AS (

        SELECT i.owner, i.table_name, SUM(s.bytes) AS bytes

        FROM dba_indexes i

        JOIN dba_segments s           ON i.owner = s.owner AND i.index_name = s.segment_name

        WHERE s.segment_type = 'INDEX'

        GROUP BY i.owner, i.table_name

    ),

    index_part_segs AS (

        SELECT i.owner, i.table_name, SUM(s.bytes) AS bytes

        FROM dba_segments s

        JOIN dba_ind_partitions ip   ON s.owner = ip.index_owner AND s.segment_name = ip.partition_name

        JOIN dba_indexes i           ON ip.index_owner = i.owner AND ip.index_name = i.index_name

        WHERE s.segment_type = 'INDEX PARTITION'

        GROUP BY i.owner, i.table_name

    ), */

    ,lob_segs AS (

        SELECT l.owner, l.table_name, SUM(s.bytes) AS bytes

        FROM dba_lobs l

        JOIN dba_segments s       ON l.owner = s.owner AND l.segment_name = s.segment_name

        WHERE s.segment_type LIKE 'LOB%'

        GROUP BY l.owner, l.table_name

    )

select * from (

SELECT

    t.owner,

    t.table_name,

    t.num_rows record_count,

    ROUND(NVL(ts.bytes, 2) / 1073741824, 2) AS table_size_GB,

    ROUND(NVL(ls.bytes, 2) / 1073741824, 2) AS log_segment_size_GB,

    /*ROUND(NVL(tp.bytes, 0) / 1073741824, 2) AS table_partition_size_GB,

    --ROUND(NVL(ix.bytes, 0) / 1073741824, 2) AS index_size_GB,

    ROUND(NVL(ip.bytes, 0) / 1073741824, 2) AS index_partition_size_GB,

*/

    ROUND((NVL(ts.bytes, 2) /* + NVL(tp.bytes, 0) + NVL(ix.bytes, 0) + NVL(ip.bytes, 0)  */ + NVL(ls.bytes, 2)) / 1073741824, 2) AS total_size_GB

FROM dba_tables t

LEFT JOIN table_segs ts         ON t.owner = ts.owner AND t.table_name = ts.table_name

/*LEFT JOIN table_part_segs tp    ON t.owner = tp.owner AND t.table_name = tp.table_name

LEFT JOIN index_segs ix         ON t.owner = ix.owner AND t.table_name = ix.table_name

LEFT JOIN index_part_segs ip    ON t.owner = ip.owner AND t.table_name = ip.table_name

*/LEFT JOIN lob_segs ls           ON t.owner = ls.owner AND t.table_name = ls.table_name

  )

  where owner in (select username from dba_users where oracle_maintained='N')

  and (record_count >= 10000 or total_size_gb >= 0.01)

  ORDER BY total_size_GB DESC;