Largest Tables Including Indexes and LOBs

Just a quick code snippit. I do a lot of data pumps to move schemas between different databases; for example taking a copy of a schema to an internal database to try to reproduce a problem. Some of these schemas have some very large tables. The large tables aren’t always needed to research a particular problem.

Here’s a quick bit of SQL to list the 20 largest tables by total size – including space used by indexes and LOBs. A quick search on google didn’t reveal anything similar so I just wrote something up myself. I’m pretty sure this is somewhat efficient; if there’s a better way to do it then let me know! I’m posting here so I can reference it in the future. :)

with segment_rollup as (
  select owner, table_name, owner segment_owner, table_name segment_name from dba_tables
    union all
  select table_owner, table_name, owner segment_owner, index_name segment_name from dba_indexes
    union all
  select owner, table_name, owner segment_owner, segment_name from dba_lobs
    union all
  select owner, table_name, owner segment_owner, index_name segment_name from dba_lobs
), ranked_tables as (
  select rank() over (order by sum(blocks) desc) rank, sum(blocks) blocks, r.owner, r.table_name
  from segment_rollup r, dba_segments s
  where s.owner=r.segment_owner and s.segment_name=r.segment_name
    and r.owner=upper('&schema_name')
  group by r.owner, r.table_name
select rank, round(blocks*8/1024) mb, table_name
from ranked_tables
where rank<=20;

The output looks like this:

Enter value for schema_name: someschema

      RANK         MB TABLE_NAME
---------- ---------- ------------------------------
         1      14095 REALLYBIGTABLE_USESLOBS
         2       6695 VERYBIG_MORELOBS
         3       5762 VERYLARGE
         4       5547 BIGBIG_LOTSOFINDEXES
         5        446 MORE_REASONABLE
         6        412 REASONABLE_TABLE_2
         7        377 ANOTHERONE
         8        296 TABLE1235
         9        280 ANOTHER_MADEUP_NAME
        10        141 I_CANT_POST_PROD_NAMES_HERE
        11         99 SMALLERTABLE
        12         92 NICESIZETABLE
        13         89 ILIKETHISTABLE
        14         62 DATATABLE
        15         53 NODATATABLE
        16         48 NOSQLTABLE
        17         30 HERES_ANOTHER_TABLE
        18         28 TINYTABLE
        19         24 ACTUALLY_THERES_400_MORE_TABLES
        20         19 GLAD_I_DIDNT_LIST_THEM_ALL

20 rows selected.

And just a quick reminder – the syntax to exclude a table from a data pump schema export is:


Hope this is useful!

About these ads

About Jeremy

Doing stuff with Oracle Database, Performance, Clusters, Linux. about.me/jeremy_schneider


3 thoughts on “Largest Tables Including Indexes and LOBs

  1. Untested for validity :-) but this is the one I use

    select owner, seg,
    when byt > 1024*1024*1024 then round(byt/1024/1024/1024)||’G’
    when byt > 1024*1024 then round(byt/1024/1024)||’M’
    else round(byt/1024)||’K’
    end,8) sz
    from (
    select s.owner, coalesce(i.table_name,l.table_name, s.segment_name) seg, sum(s.bytes) byt
    from dba_segments s,
    dba_indexes i,
    dba_lobs l
    where s.owner = i.owner(+)
    and s.segment_name = i.index_name(+)
    and s.owner = l.owner(+)
    and s.segment_name = l.segment_name(+)
    group by s.owner, coalesce(i.table_name,l.table_name, s.segment_name)
    order by 3 desc
    where byt/1024/1024 >= nvl(‘&threshold_mb’,byt/1024/1024)

    Posted by connormcdonald | December 4, 2013, 10:37 pm
    • outer-join with group-by-coalesce – very clever way to approach this. if i have a chance i’ll have to compare that with my subquery-union-all approach and see which is more efficient. it’s always nice to know that my code is having the least possible impact on the database where i’m running it!

      i also want to update the query to include partitions; the database i was working on yesterday didn’t have any.

      Posted by Jeremy | December 5, 2013, 8:01 am
  2. Hello Jeremy, really nice script !

    But ” round(blocks*8/1024) mb” gives the wrong result if there are some block sizes in DB.

    select distinct TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces;

    Posted by Yury Pudovchenko | October 18, 2014, 12:41 pm


(a) This is my personal blog.

The views expressed on this website are mine alone and do not necessarily reflect the views of my employer.



Jeremy Schneider

Get every new post delivered to your Inbox.

Join 926 other followers

%d bloggers like this: