>
Technical

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:

expdp ... exclude=SCHEMA_EXPORT/TABLE:[TABNAME],[TABNAME],...

Hope this is useful!

About these ads

About Jeremy

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

Discussion

3 thoughts on “Largest Tables Including Indexes and LOBs

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

    select owner, seg,
    lpad(case
    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

Disclaimer

(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.

racattack-iconOakTableLogo-small

about.me

Jeremy Schneider
Follow

Get every new post delivered to your Inbox.

Join 926 other followers

%d bloggers like this: