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!
Ardent Performance Computing 
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
Posted by connormcdonald | December 4, 2013, 10:37 pmouter-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
Posted by Jeremy | December 5, 2013, 8:01 amHello 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
Posted by Yury Pudovchenko | October 18, 2014, 12:41 pm