Friday, 22 May 2015

How to find TABLE size?

set linesize 120
set pagesize 160
col table_name format a30
select
    owner,
    Table_name,
    sum(bytes) Bytes,
    Round(sum(bytes)/1024,2) as KB,
    Round(sum(bytes)/1024/1024,2) as MB,
    Round(sum(bytes)/1024/1024/1024,2) as GB
from
    (Select
    owner,
    segment_name as Table_name,
    bytes
    from
    dba_segments
    union all
    SELECT
    i.table_name,
    i.owner,
    s.bytes
    FROM
    dba_indexes i,
    dba_segments s
    WHERE
    s.segment_name = i.index_name
    AND  
    s.owner = i.owner
    AND  
    s.segment_type = 'INDEX'
    UNION ALL
    SELECT
    l.table_name,
    l.owner,
    s.bytes
    FROM
    dba_lobs l,
    dba_segments s
    WHERE
    s.segment_name = l.segment_name
    AND  
    s.owner = l.owner
    AND  
    s.segment_type = 'LOBSEGMENT'
    UNION ALL
    SELECT
    l.table_name,
    l.owner,
    s.bytes
    FROM
    dba_lobs l,
    dba_segments s
    WHERE
    s.segment_name = l.index_name
    AND  
    s.owner = l.owner
    AND  
    s.segment_type = 'LOBINDEX')
WHERE
    owner in UPPER('&owner')
    and
    table_name in upper('&table_name')
    GROUP BY
    table_name,
    owner
    order by
    MB desc;

No comments:

Post a Comment