oracle list partitioned tables

1SELECT * FROM ALL_TAB_PARTITIONS;

1-- List of partitioned tables
2SELECT DISTINCT P.TABLE_OWNER, P.TABLE_NAME, count(*) AS NB_PARTITIONS, 
3	SP.NB_SUBPARTITIONS
4FROM DBA_TAB_PARTITIONS P
5LEFT JOIN (SELECT DISTINCT TABLE_OWNER, TABLE_NAME, count(*) AS NB_SUBPARTITIONS
6           FROM DBA_TAB_SUBPARTITIONS
7           GROUP BY TABLE_OWNER, TABLE_NAME, PARTITION_NAME
8          ) SP
9ON P.TABLE_OWNER = SP.TABLE_OWNER AND P.TABLE_NAME = SP.TABLE_NAME
10WHERE P.TABLE_OWNER NOT IN ('SYS', 'SYSTEM')
11GROUP BY P.TABLE_OWNER, P.TABLE_NAME, SP.NB_SUBPARTITIONS
12ORDER BY P.TABLE_OWNER, P.TABLE_NAME;