drop hourly list partition

Question

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')

The table definition would help a lot. – William Robertson Aug 20 '22 at 10:57 — William Robertson, Aug 20 '22 at 10:57

score 3 · Accepted Answer · answered Aug 18 '22 at 09:51

3

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?

answered Aug 18 '22 at 09:51

Wernfried Domscheit

54,457
9
76
110

it shows ORA-14702; ORA-06512; The partition number is invalid or out-of-range – Andrey Romanov Aug 18 '22 at 10:05
Like in your previous question it is very hard to provide any solution if you don't provide the table definition. Based on the information given in your question, this is a working solution. Otherwise have a look at liked answer. – Wernfried Domscheit Aug 18 '22 at 10:33

drop hourly list partition

1 Answers1