Hello, Am trying to collect stats for current month partition and previous month for all the tables with a script like below , In my DB we have partitions created for whole year in advance, but whenever I run this, this picks up that last most max partition say eg: DEC_2021 and collect stats for DEC_2021 & NOV_2021 where no data availble I need like current month based on sysdate.

I tried to use High value to identify current month partition with sydate, as it a long datatype, there were lot of limitations.

Experts please shed some light. Thanks in Advance.

DECLARE
//Few variables for looping and partition name

BEGIN
 FOR b IN  (
                select table_owner, table_name, PARTITION_NAME
                from dba_tab_partitions where table_owner = SCHEMA
    order by table_owner, table_name, partition_position DESC
            )
LOOP
// using counter to loop every first 2 partitions of all tables that fetched above
END LOOP;

END;

select *
from (
select table_owner, table_name, PARTITION_NAME, row_number() over ( partition by table_owner, table_name order by partition_position desc ) as seq
 from dba_tab_partitions where table_owner = SCHEMA
order by table_owner, table_name, partition_position DESC
)
where seq <= 2