ORACLE - Get database size per partition

Question

I am looking for an SQL query to give me that kind of output:

table_owner  table_name  partition_name  data (bytes)  indexes (bytes)
MY_OWNER     MY_TABLE    P01             12345678      23456789
MY_OWNER     MY_TABLE    P02             34567890      45678901
MY_OWNER     MY_TABLE    P03             56789012      67890123
...

I visited How do I calculate tables size in Oracle but didn't find what I am looking for, because it is always per table, per owner/schema but never per partition.

I know that the amount in bytes may not really be representative of the reality since it could be "real bytes in disk" and/or "bytes preallocated" (and real data could only use 1% of this preallocated space), but I am open to have this value even if it is the preallocated or the real amount of bytes used.

Notes:

• Using Oracle 18c Enterprise
• We do NOT care about system tables, ORACLE tables, maintenance tables, etc. just focusing on tables created by myself for the data
• Tablespace name is ALWAYS the same for all partitions in all tables in the same schema, and of course each tablespace name is different for each schema
• No need to round in Kb, Mb, Gb or even Tb, if possible I would prefer it in bytes.
• No need of pourcentage, no need of max space available
• I only use partitions, NOT any subpartitions
• I can use a PL/SQL block if we need to loop on each schema, table and partition
• Guessing I can run this query by any user

Any idea?

Answer 1

Hi something like this? We use a similar query that I've modified. You would have to change your ORDER BY clause as you wish, and maybe limit it to one owner as it could take long over the whole database. For that reason we don't join to segments for size, but only look at rows and blocks.

select "OWNER", "OBJECT", "TYPE", "PARTITION_NAME", "NUM_ROWS", "GB", "PARTITION_POSITION", "DUMMY"
from ((select tp.table_owner owner, tp.TABLE_NAME object, 'TABLE' type, tp.PARTITION_NAME,ROUND(s.bytes/1024/1024/1024, 2) GB, tp.NUM_ROWS,tp.PARTITION_POSITION, 1 dummy
  from dba_tab_partitions tp, dba_tables t, dba_segments s
  where t.table_name=tp.table_name and t.table_name=s.segment_name )
union 
 (select ip.index_owner owner,ip.INDEX_NAME object, 'INDEX' type, ip.PARTITION_NAME,ROUND(s.bytes/1024/1024/1024, 2) GB,ip.NUM_ROWS, ip.PARTITION_POSITION, 2 dummy
  from dba_ind_partitions ip, dba_indexes i, dba_tables t, dba_segments s
   where i.index_name=ip.index_name and i.table_name=t.table_name and i.index_name=s.segment_name )
union 
(select lp.table_owner owner,lp.LOB_NAME object, 'LOB' type, lp.PARTITION_NAME, 0, ROUND(s.bytes/1024/1024/1024, 2) GB,lp.PARTITION_POSITION, 3 dummy 
from dba_lob_partitions lp, dba_tables t, dba_segments s
  where t.table_name=lp.table_name and t.table_name=s.segment_name ))
order by 8,1,2,7;