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?

what about unpartitioned tables? What about tables with indexes that have partitions is different tablespace[s]? joining dba_segments with dba_indexes would bring you close. — user123664
– user123664, Commented Jul 7, 2021 at 12:05
@ik_zelf: Indeed, so first we do not care about unpartioned tables because all MY tables are partitioned for sure (at least, if new schema, they have at least one partition whatever is the reason) Second tablespace name is always the same into the whole schema, and each schema/owner has a unique tablespace name — user12642493
– user12642493, Commented Jul 7, 2021 at 12:24
@ik_zelf Thanks for noticing these points that I didn't clarify — user12642493
– user12642493, Commented Jul 7, 2021 at 12:27

sandman · Accepted Answer · 2021-07-07 14:02:17Z

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;

answered Jul 7, 2021 at 14:02

sandman

2,11812 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user12642493 Over a year ago

Thanks @sandman. It seems awesome and working. However, for the INDEX part, I cannot link the partition names. I had this for TABLE and LOB: MY_OWNER MY_TABLE INDEX P1 ... However, for INDEX, I have something like: MY_OWNER MY_TABLE INDEX SYS_IL_Pxxxxxx ... Why ? Why can't I have the same for INDEX ? Is there a limitation to not be able to match a SYS_IL_Pxxxxx with the real table name like Pxxx ?

user12642493 Over a year ago

Okay, according to docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/… , the SYS_IL_Pxxxxxx means that I didn't set a partition name for indexes, which seems true since I never set any name. Solution is then correct but could be adapter, see answer above/below

ChrisG Over a year ago

I tried running this on 12.2 and had to add "and tp.partition_name = s.partition_name" to the first where clause, and make similar additions to the other two selects to avoid spurious additional matches.

Collectives™ on Stack Overflow

ORACLE - Get database size per partition

Notes:

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Notes:

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related