SQL to list missing partition in Oracle

user_18963075 0
Viewed 27

The SQL below list the partitions for a partitioned table

select partition_name
from   dba_tab_partitions
where  table_name = 'ABCD'
order by partition_name
;


PARTITION_NAME
------------------------------
P_201511
P_201512
P_201601
P_201602
P_201603
P_201604
P_201605
P_201606
P_201607
P_201608
P_201609
P_201610
P_201611
P_201612
P_201701
P_201702
P_201703
P_201704
P_201705
P_201706
P_201707
P_201708
P_201709
P_201710
P_201711
P_201712
P_201801
P_201802
P_201803
P_201804
P_201805
P_201806
P_201807
P_201808
P_201809
P_201810
P_201811
P_201812
P_201901
P_201902
P_201903
P_201904
P_201905
P_201906
P_201907
P_201908
P_201909
P_201910
P_201911
P_202207
P_202208
P_202209

52 rows selected.

As you can see the partitions 201912 to 202206 are missing. Does anyone knows any SQL trick that will able to list the missing partitions maybe on a second field or maybe list the missing partition in bracket so it list it like below for sample

P_201909
P_201910
P_201911
[P_201912]
[P_202001]
[P_202002]
...
...
[P_202205]
[P_202206]
P_202207
P_202208
partition oracle sql
1 Answers

Here's one option; read comments within code.

SQL> WITH
  2     -- min and max dates from partition names for the ABCD table
  3     period (min_date, max_date)
  4     AS
  5        (SELECT MIN (TO_DATE (SUBSTR (partition_name, 3), 'yyyymm')),
  6                MAX (TO_DATE (SUBSTR (partition_name, 3), 'yyyymm'))
  7           FROM dba_tab_partitions
  8          WHERE table_name = 'ABCD'),
  9     all_partitions (partition_name)
 10     -- create all possible partition names between MIN and MAX date
 11     AS
 12        (    SELECT 'P_' || TO_CHAR (ADD_MONTHS (min_date, LEVEL - 1), 'yyyymm')
 13               FROM period
 14         CONNECT BY LEVEL <= MONTHS_BETWEEN (max_date, min_date) + 1)
 15    -- finally, outer join generated partition names to "real" partition names and
 16    -- set indicator for missing partitions
 17    SELECT a.partition_name || CASE WHEN d.partition_name IS NULL THEN '*' END partition_name
 18      FROM all_partitions a
 19           LEFT JOIN dba_tab_partitions d
 20              ON     d.partition_name = a.partition_name
 21                 AND d.table_name = 'ABCD'
 22  ORDER BY a.partition_name;

PARTITION_NAME
--------------------
P_201511
P_201512
P_201601
P_201602
P_201603
<snip>
P_201909
P_201910
P_201911
P_201912*
P_202001*
P_202002*
P_202003*
P_202004*
P_202005*
P_202006*
<snip>
P_202202*
P_202203*
P_202204*
P_202205*
P_202206*
P_202207
P_202208
P_202209

83 rows selected.

SQL>
user_90979060
Related