TOP N PARTITION FROM EACH PARTITION TABLE

 TOP N PARTITION NAMES FROM EACH PARTITION TABLE IN SCHEMA:

SELECT

    table_name,

    LISTAGG(partition_name, '   ') WITHIN GROUP(

        ORDER BY

            partition_position

    ) AS partition_names

FROM

    (

        WITH rws AS (

            SELECT

                o.*,

                ROW_NUMBER() OVER(

                    PARTITION BY table_name

                    ORDER BY

                        partition_position DESC

                ) rn

            FROM

                user_tab_partitions o

        )

        SELECT

            table_name,

            partition_name,

            partition_position

        FROM

            rws

        WHERE

            rn <= 10

        ORDER BY

            table_name,

            partition_position

    )

GROUP BY

    table_name

ORDER BY

    table_name;

It will display top 10 partitions of each table in a schema.

Output will have 1 row per each table.