Popular Posts

Monday, July 21, 2025

Script Oracle User Size including table, index, lob and partitions

 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

    ) ,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

    )  ,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_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 = 'LOBSEGMENT'

        GROUP BY l.owner, l.table_name

    ),lob_ind 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 = 'LOBINDEX'

        GROUP BY l.owner, l.table_name

    ), tot as (

select * from (

SELECT

sys_context ('USERENV', 'DB_NAME')db_name,

    t.owner,

    t.table_name,

    t.num_rows record_count,

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

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

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

    ROUND(NVL(li.bytes, 2) / 1073741824, 2) AS lob_index_size_GB,

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

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

    ROUND((NVL(ts.bytes, 2)  + NVL(tp.bytes, 0)  + NVL(ip.bytes, 0)  + NVL(ix.bytes, 0) + NVL(ls.bytes, 2) + nvl(li.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 index_segs ix         ON t.owner = ix.owner AND t.table_name = ix.table_name

LEFT JOIN table_part_segs tp    ON t.owner = tp.owner AND t.table_name = tp.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

left join lob_ind li            ON t.owner = li.owner AND t.table_name = li.table_name

  )

  where

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

  ( total_size_gb >= 1 /*OR record_count >= 10000 */)

  ORDER BY owner , total_size_GB DESC

  )

  select DB_NAME, OWNER, TABLE_NAME, RECORD_COUNT,TOTAL_SIZE_GB, TABLE_SIZE_GB, INDEX_SIZE_GB, LOB_SEGMENT_SIZE_GB, LOB_INDEX_SIZE_GB, TABLE_PART_SIZE_GB, INDEX_PART_SIZE_GB

  From tot 

  ;

No comments:

Post a Comment