How to list subpartitions and their sizes

First, find the number of partitions and subpartitions in a specific tablespace:

SELECT S.TABLE_NAME, S.TABLESPACE_NAME,COUNT(DISTINCT S.PARTITION_NAME) "num partitions", COUNT(DISTINCT S.SUBPARTITION_NAME) "num subpartitions"
FROM DBA_TAB_SUBPARTITIONS S 
WHERE S.TABLE_OWNER='SCOTT'
GROUP BY S.TABLE_NAME, S.TABLESPACE_NAME;

TABLE_NAME	TABLESPACE_NAME	num partitions	num subpartitions
MYTABLE1	DATA1	75	450
MYTABLE2	DATA2	73	219
MYTABLE3	DATA1	74	222
MYTABLE4	DATA2	74	222
MYTABLE5	DATA1	81	243

For a specific table, all partitions and their subpartitions:

SELECT P.PARTITION_NAME, P.SUBPARTITION_NAME, S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.SEGMENT_NAME='MYTABLE'
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
ORDER BY P.PARTITION_NAME, P.SUBPARTITION_NAME, S.BYTES DESC;

PARTITION_NAME	SUBPARTITION_NAME	SEGMENT_TYPE	TABLESPACE_NAME	COMPRESSION	COMPRESS_FOR	NUM_ROWS	LAST_ANALYZED	gb
SYS_P177766	SYS_SUBP177763	TABLE SUBPARTITION	DATA2	DISABLED		26021384	25.06.2020 22:55:36	12
SYS_P177766	SYS_SUBP177764	TABLE SUBPARTITION	DATA2	DISABLED		21531914	26.06.2020 23:32:34	9,1
SYS_P177766	SYS_SUBP177765	TABLE SUBPARTITION	DATA2	DISABLED		0	02.03.2020 00:56:19	0
SYS_P177774	SYS_SUBP177771	TABLE SUBPARTITION	DATA2	DISABLED		285	16.07.2020 23:21:12	0
SYS_P177774	SYS_SUBP177772	TABLE SUBPARTITION	DATA1	DISABLED		259	16.07.2020 23:21:30	0

For specific subpartitions:

SELECT P.PARTITION_NAME "Subpartition name", S.PARTITION_NAME,S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
AND P.SUBPARTITION_NAME in ('SYS_SUBP177773','SYS_SUBP177763','SYS_SUBP177764','SYS_SUBP177765');

PARTITION_NAME	Subpartition name	SEGMENT_TYPE	TABLESPACE_NAME	COMPRESSION	COMPRESS_FOR	NUM_ROWS	LAST_ANALYZED	gb
SYS_P177766	SYS_SUBP177763	TABLE SUBPARTITION	DATA1	DISABLED		26021384	25.06.2020 22:55:36	12
SYS_P177766	SYS_SUBP177764	TABLE SUBPARTITION	DATA1	DISABLED		21531914	26.06.2020 23:32:34	9,1
SYS_P177766	SYS_SUBP177765	TABLE SUBPARTITION	DATA1	DISABLED		0	02.03.2020 00:56:19	0
SYS_P177774	SYS_SUBP177773	TABLE SUBPARTITION	DATA1	DISABLED		0	02.03.2020 00:56:20	0

For a specific partition:

SELECT S.PARTITION_NAME "Subpartition name", S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.SEGMENT_NAME='MYTABLE'
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
AND P.PARTITION_NAME ='SYS_P14675'
ORDER BY S.BYTES DESC;

Subpartition name	SEGMENT_TYPE	TABLESPACE_NAME	COMPRESSION	COMPRESS_FOR	NUM_ROWS	LAST_ANALYZED	gb
SYS_SUBP14672	TABLE SUBPARTITION	DATA1	DISABLED		127103150	29.06.2019	51,4
SYS_SUBP14673	TABLE SUBPARTITION	DATA1	DISABLED		89059917	29.06.2019	34,1
SYS_SUBP14674	TABLE SUBPARTITION	DATA1	DISABLED		0	29.06.2019	0

For a specific tablespace:

SELECT S.PARTITION_NAME "Subpartition name",S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.OWNER = 'SCOTT'
AND S.TABLESPACE_NAME='DATA1'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
ORDER BY S.BYTES DESC; 

Subpartition name	SEGMENT_TYPE	TABLESPACE_NAME	COMPRESSION	COMPRESS_FOR	NUM_ROWS	LAST_ANALYZED	gb
SYS_SUBP27395	TABLE SUBPARTITION	SCOTT	DISABLED		100835414	27.10.2018 19:06:53	44,6
SYS_SUBP29902	TABLE SUBPARTITION	SCOTT	DISABLED		88951135	27.10.2018 21:23:24	41,6
SYS_SUBP29332	TABLE SUBPARTITION	SCOTT	DISABLED		83142250	27.10.2018 23:40:00	38,3
SYS_SUBP29395	TABLE SUBPARTITION	SCOTT	DISABLED		78610455	27.10.2018 19:00:05	37,1
SYS_SUBP28115	TABLE SUBPARTITION	SCOTT	DISABLED		75810738	07.07.2018 15:54:52	35,7