title: How to drop partitions based on specific retention period?
body: <pre><code>    PROCEDURE purge_partitions
     (
        p_owner            IN VARCHAR2
       ,p_name             IN VARCHAR2
       ,p_retention_period IN NUMBER
     ) IS
     BEGIN
        FOR partition_rec IN (SELECT partition_name
                                    ,high_value
                                FROM dba_tab_partitions
                               WHERE table_owner = p_owner
                                 AND table_name = p_name)
  
        --drop partitions older than specified retention preriod
        LOOP
           IF SYSDATE &gt;= add_months(to_date(substr(partition_rec.high_value
                                                  ,12
                                                  ,19)
                                           ,'YYYY-MM-DD HH24:MI:SS')
                                   ,p_retention_period)
           THEN
              execute_immediate('ALTER TABLE ' || p_owner || '.' ||
                                p_name || ' DROP PARTITION ' ||
                                partition_rec.partition_name);
           END IF;
        END LOOP;
     END purge_partitions;
  </code></pre>
  
  <p>I am trying to add futher granularity to the loop statement so rather than just dropping partitions monthly, I'd like to extend this by days too. Also just FYI the records are fetched from a Config tabe which includes details of partitioned tables(table owner, name and retention period - data type NUMBER). Therefore, im not sure how to go about this. Any help is highly appreciated.  </p>