drop hourly list partition-CodePudding

I have a start_time field of type date and have created a list hourly partition called start_time_h of type to_char(start_time, 'hh'). But now it’s impossible to drop the partition in any way, I tried it through execute immediate, directly through alter table. Every time an error occurs that the rage is different, etc., how can I drop an hourly partition from trunc(sysdate - 1/24, 'hh') to trunc(sysdate 1/24, 'hh'), that is, it needs to drop two partitions since I insert data from trunc(sysdate - 1/24, 'hh') to trunc(sysdate 1/24, 'hh')

CodePudding user response：

Based on the information you provided, I would suggest this one:

DECLARE

    start_time DATE := TRUNC(SYSDATE - 1/24, 'hh');
    end_time DATE:= TRUNC(SYSDATE   1/24, 'hh');
    aDate DATE;

BEGIN

    aDate := start_time ; 
    LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE SOFT_CALLS DROP PARTITION FOR (TO_DATE('''|| TO_CHAR(aDate, 'YYYY-MM-DD HH24:MI')||''', ''YYYY-MM-DD HH24:MI''))';
        aDate := aDate   1/24;
        exit WHEN aDate >= end_time ;
    END LOOP;

END;

Another approach is this one: How to drop multiple interval partitions based on date?

CodePudding user response：

Did you try doing:

SELECT num_rows, partition_name, subpartition_count
  FROM all_tab_partitions
 WHERE table_name = <your_table>;

to retrieve the partitions and then just do:

ALTER TABLE <your_table> DROP PARTITION <the_partition_in_question>;

and then just create what you need.