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!
Recent comments
2 years 52 weeks ago
3 years 12 weeks ago
3 years 16 weeks ago
3 years 17 weeks ago
3 years 21 weeks ago
3 years 43 weeks ago
4 years 11 weeks ago
4 years 40 weeks ago
5 years 25 weeks ago
5 years 25 weeks ago