We have this DW test environment, one request is to list all table sizes for specific schemas. The table size in the report should already include the size of all it's index. We only need to report tables having 1Million rows or more based on num_rows estimate. (These tables will then be reviewed for size usage and are candidate for rows purging).
I have made two separate queries, one for table size and one for index sizes.
--Table Size
select seg.owner, seg.segment_name, seg.bytes/1024/1024 as table_size_mb, tab.num_rows
from dba_segments seg join dba_tables tab
on (seg.segment_name = tab.table_name)
where seg.owner in
(
'JAMES',
'JOHN',
'MARIA'
)
and NVL(tab.NUM_ROWS,0) > 1000000;
---Index Size
SELECT seg.owner, idx.table_name, idx.index_name, SUM(seg.bytes)/1024/1024 as index_size_mb
FROM dba_segments seg,
dba_indexes idx
WHERE idx.table_owner in
(
'JAMES',
'JOHN',
'MARIA'
)
AND idx.table_name IN
(
select table_name from dba_tables where nvl(num_rows,0) > 1000000
)
AND idx.owner = seg.owner
AND idx.index_name = seg.segment_name
GROUP BY seg.owner, idx.index_name, idx.table_name;
I cannot figure out a way of combining the query results so that I can have a report that is something like this:
TABLE_OWNER TABLE_NAME SIZE_MB NUM_ROWS
James Customer_Tb1 120 2000000
James Customer_Tb2 100 1100000
James Customer_Tb3 300 3200000
John Orders_Tb1 545 1300000
John Orders_Tb2 900 1500000
Maria Recept_Tb1 750 6700000
SIZE_ MB is the table size plus the size of all its indexes
NUM_ROWS is the row estimate of the table that should be 1M or more in order to be qualified in the report