Hello ,
on 12c , I am trying to create a query that would show : table name, table size, row count , and size of all indexes for each of those tables in the same row.
I got everything except index size, can you please help me with advice ?
So this shows informations that I need but sum of the segments for all indexes for each table is not correct.
WITH
aa
AS
( SELECT a.table_name ee,
a.num_rows rr,
ROUND (SUM (b.bytes) / 1024 / 1024, 2) ab
FROM dba_tables a, dba_segments b
WHERE a.TABLE_NAME = b.segment_name
AND a.num_rows IS NOT NULL
AND b.segment_type NOT IN ('TYPE2 UNDO',
'ROLLBACK',
'CLUSTER',
'INDEX PARTITION',
'INDEX')
GROUP BY a.table_name, a.num_rows
ORDER BY a.num_rows DESC NULLS LAST)
SELECT ee,
rr,
ab,
ROUND (SUM (c.bytes) / 1024 / 1024, 2)
FROM dba_segments c, aa, dba_indexes d
--WHERE c.segment_type IN ('INDEX PARTITION', 'INDEX')
--and c.segment_name=aa.ee
where c.segment_name IN (SELECT index_name
FROM dba_indexes
WHERE table_name = aa.ee)
GROUP BY ee, rr, ab