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!
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)
LikeLike
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.
LikeLike
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;
LikeLike