How to list partitions and their sizes

Using an inline view with a correlated subquery:

set lines 200
col partition_name format a40
SELECT P.PARTITION_NAME, (  SELECT ROUND(SUM(BYTES)/1024/1024/1024)
                            FROM DBA_SEGMENTS S
                            WHERE S.PARTITION_NAME = P.PARTITION_NAME
                            AND SEGMENT_NAME='&&TABLE_NAME') "size GB"
FROM DBA_TAB_PARTITIONS P
WHERE P.TABLE_NAME = '&&TABLE_NAME'
ORDER BY P.PARTITION_POSITION ASC;

Example output:

sqlplus / as sysdba @get_size.sql
Enter value for table_name: ARCHIVED_DOCUMENTS


PARTITION_NAME                      size GB
------------------------------   ----------
DOKARCHIVE1                           2.875
DOKARCHIVE2                               3
DOKARCHIVE3                               3
DOKARCHIVE4                               3
DOKARCHIVE5                               3
DOKARCHIVE6                          2.8125
DOKARCHIVE7                            2.75

Or, limit the output to specific partitions:

select s.partition_name,s.tablespace_name, p.compression,p.num_rows,Round(sum(s.bytes)/1024/1024/1024,1) "GB"
from dba_segments S join dba_tab_partitions p
on (s.partition_name = p.partition_name)
where s.segment_name='SALES_ARCHIVE'
and   s.owner='SH'
and s.partition_name in ( 'P_2015_01','P_2016_01','P_2017_01')
group by s.partition_name,s.tablespace_name,p.compression,p.num_rows
order by 1;

Example output:

PARTITION_NAME	TABLESPACE_NAME	COMPRESSION	NUM_ROWS	GB
P_2015_01	SALES_2015	ENABLED	22077874	2,3
P_2016_01	SALES_2016	ENABLED	394694450	42,3
P_2017_01	SALES_2017	ENABLED	481708328	48,9

If your partitions involve lob columns, make sure you fetch the sizes of the lob partitions, too.