7 Replies Latest reply on Mar 19, 2012 3:36 PM by rp0428

    Drop statement for an interval partition

    794960
      I have an Interval partition (monthly) table that will keep a history for 6 month. After 6 months I need to roll off the oldest partition and create a new one.

      We have a job that drops the oldest partition by using

      alter table drop partition for <date> ;

      One time because of some server issues the job ran twice and it dropped the oldest partition again. So effectively it dropped 2 partitions.

      Question: I was under the impression that "alter table drop partition for('01-APR-2010') " will only drop partition only for that month but looks like its dropping May partition too when run again.

      Anyone face this issue before

      Thanks
        • 1. Re: Drop statement for an interval partition
          james m
          the job ran twice and it dropped the oldest partition again.
          How does the job determine what partition to drop? From the sounds of it, the job selects the oldest partition that is available when it runs. If so, every time it runs it drops the "oldest" partition.

          Check that.
          • 2. Re: Drop statement for an interval partition
            damorgan
            You can not drop the oldest, root, partition in an interval partitioned table. That is clearly spelled out in the docs.

            What I do when creating interval partitioned tables is create the initial partition with an impossibly old date that can be ignored for all of time and will hold no rows. Then I maintain the table by always working against partition_position = 2 as the starting point.
            • 3. Re: Drop statement for an interval partition
              james m
              Thanks for pointing that out. I learn something new every day on this forum.

              The intent of my post was to check how the job determines what partition to drop. Since it is scheduled, it has to figure it out somehow on its own. Likely it drops the partition with partition_position 2 as you explained in your example. Since it is date based, the job should probably figure out the date 6 months in the past and attempt to drop it. That way it will fail if it is already dropped.

              Thanks much,
              - James
              • 4. Re: Drop statement for an interval partition
                796829
                statement 'alter table_name drop partition for <date>;' works fine on interval partition table, even if you run twice, it won't drop two partitions

                But, if the table is not an interval partition table, the statement executed more than once may drop more partitions.

                Edited by: cathy on Mar 16, 2012 12:04 AM
                • 5. Re: Drop statement for an interval partition
                  >
                  statement 'alter table_name drop partition for <date>;' works fine on interval partition table, even if you run twice, it won't drop two partitions
                  >
                  cathy - why are you resurrecting this 2 year old thread?
                  That statement is wrong so I figured I would just clear up it up. Please don't respond to this. If you have any questions or followup start a new thread.
                  Please try things before you post

                  Running the alter multiple times will keep dropping a partition (until only two exist) because when a partition is dropped the next one now covers the date range of the one that was dropped in addition to the date range it covered before.

                  damorgan said you can't drop the oldest partition but the example below shows you can.
                  This code creates four partitions
                  drop table DEMO_INTERVAL_DATA_LOAD1
                  
                  CREATE TABLE DEMO_INTERVAL_DATA_LOAD1 (
                                  ROLL_NUM        NUMBER(10),
                                  CLASS_ID        NUMBER(2),
                                  ADMISSION_DATE  DATE,
                                  TOTAL_FEE       NUMBER(4),
                                  COURSE_ID       NUMBER(4))
                                  PARTITION BY RANGE (ADMISSION_DATE)
                                  INTERVAL (NUMTOYMINTERVAL(3,'MONTH'))
                                  ( PARTITION QUAT_1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','DD-MON-YYYY')),
                                   PARTITION QUAT_2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')),
                                   PARTITION QUAT_3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','DD-MON-YYYY')),
                                   PARTITION QUAT_4_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','DD-MON-YYYY')));
                    
                   ALTER TABLE DEMO_INTERVAL_DATA_LOAD1 ADD CONSTRAINT IDX_DEMO_ROLL1 PRIMARY KEY (ROLL_NUM);
                   -- now insert records to the partitions
                  
                   INSERT INTO DEMO_INTERVAL_DATA_LOAD1 VALUES (10,1,'12-MAR-2012',1000,90);
                   INSERT INTO DEMO_INTERVAL_DATA_LOAD1 VALUES (11,5,'01-JUN-2012',5000,80);
                   INSERT INTO DEMO_INTERVAL_DATA_LOAD1 VALUES (12,9,'12-SEP-2012',4000,20);
                   INSERT INTO DEMO_INTERVAL_DATA_LOAD1 VALUES (13,7,'29-DEC-2012',7000,10);
                    -- insert a record for a date that needs a new partition 
                   INSERT INTO DEMO_INTERVAL_DATA_LOAD1 VALUES (14,8,'21-JAN-2013',2000,50);
                  If you now execute this ALTER statement it will drop the oldest partition (QUAT_1_2012)
                  alter table DEMO_INTERVAL_DATA_LOAD1 drop partition for (TO_DATE('01-APR-2000','DD-MON-YYYY'))
                  If you do the ALTER again it will now drop QUAT_2_2012
                  Once more drops QUAT_3_2012
                  Yet again gives an exception
                  ORA-14758: Last partition in the range section cannot be dropped
                  You can see the partitions to see what gets dropped after each ALTer by running this query
                  SELECT TABLE_OWNER,
                             TABLE_NAME,
                             COMPOSITE,
                             PARTITION_NAME,
                         PARTITION_POSITION,
                            TABLESPACE_NAME,
                         LAST_ANALYZED
                  FROM ALL_TAB_PARTITIONS
                      WHERE TABLE_OWNER='SCOTT'
                     AND TABLE_NAME='DEMO_INTERVAL_DATA_LOAD1'
                     ORDER BY PARTITION_POSITION;
                  Not exactly intutive but when you remove a partition the next later one now covers that date range and because the date range is now covered by a partition the old range will NOT be recreated if you add a record that orginally would have gone into it.
                  • 6. Re: Drop statement for an interval partition
                    spajdy
                    I used following script:
                    create table daily_interest(
                    id NUMBER(22) NOT NULL,
                    id_credit NUMBER(22) NOT NULL,
                    acc_date DATE NOT NULL,
                    int_date DATE NOT NULL,
                    interest NUMBER(16,2) NOT NULL,
                    type VARCHAR2(1 CHAR) NOT NULL,
                    id_transaction NUMBER(22),
                    CONSTRAINT pk_daily_interest PRIMARY KEY(id))
                    partition by range(acc_date)
                    interval (NUMTOYMINTERVAL(1, 'MONTH')) 
                    (partition p1 values less than (to_date('1.5.2011','dd.mm.yyyy')));
                    
                    create index ldaily_interest_id_credit on daily_interest(id_credit)  local;
                    create index ldaily_interest_id_transaction on daily_interest(id_transaction)  local;
                    
                    insert into daily_interest
                    values(1,1,to_date('1.4.2000','dd.mm.yyyy'),sysdate,1,'x',null);
                    insert into daily_interest
                    values(2,1,to_date('1.5.2011','dd.mm.yyyy'),sysdate,1,'x',null);
                    insert into daily_interest
                    values(3,1,to_date('1.6.2011','dd.mm.yyyy'),sysdate,1,'x',null);
                    insert into daily_interest
                    values(4,1,to_date('3.7.2011','dd.mm.yyyy'),sysdate,1,'x',null);
                    insert into daily_interest
                    values(5,1,to_date('2.8.2011','dd.mm.yyyy'),sysdate,1,'x',null);
                    insert into daily_interest
                    values(6,1,to_date('11.9.2011','dd.mm.yyyy'),sysdate,1,'x',null);
                    insert into daily_interest
                    values(7,1,to_date('21.10.2011','dd.mm.yyyy'),sysdate,1,'x',null);
                    
                    select * from daily_interest;
                    
                    
                    select * from dba_tab_partitions tp
                    where tp.table_name='DAILY_INTEREST';
                    
                    DECLARE
                        v_date     DATE;
                        v_date_cut DATE := add_months(trunc(SYSDATE, 'mm'), -3);
                    BEGIN
                        FOR r IN (SELECT *
                                  FROM   dba_tab_partitions tp
                                  WHERE  tp.table_name = 'DAILY_INTEREST'
                                  AND    tp.table_owner = 'EOM')
                        LOOP
                            EXECUTE IMMEDIATE 'SELECT ' || r.high_value || ' from dual'
                                INTO v_date;
                            IF v_date < v_date_cut
                            THEN
                                IF r.interval = 'NO'
                                THEN
                                    EXECUTE IMMEDIATE 'alter table ' || r.table_owner || '.' || r.table_name || ' truncate partition ' ||
                                                      r.partition_name;
                                ELSE
                                    EXECUTE IMMEDIATE 'alter table ' || r.table_owner || '.' || r.table_name || ' drop partition ' ||
                                                      r.partition_name;
                                END IF;
                            END IF;
                        END LOOP;
                        EXECUTE IMMEDIATE 'alter index pk_daily_interest rebuild';
                    END;
                    /
                    The first partiton cann't be droped so I truncate it.
                    Others partition I drop. All global indexes (in my case index on primary key) must be rebuild.
                    • 7. Re: Drop statement for an interval partition
                      >
                      The first partiton cann't be droped so I truncate it.
                      Others partition I drop
                      >
                      Interesting - the rule appears to be a little less obvious than it seems.
                      This is the error you get if you try to delete your 'first' partition
                      >
                      ORA-14758: Last partition in the range section cannot be dropped
                      >
                      and damorgan wrote
                      >
                      You can not drop the oldest, root, partition in an interval partitioned table
                      >
                      I was able to drop the first partition in my example

                      After further testing it appears that 'Last partition' (or root partition) actually means the 'Last' partition created in the CREATE TABLE statement.
                      You only created one partition in your CREATE TABLE statement so it is the 'Last' and cannot be deleted.

                      I created 4 partitions in my CREATE TABLE
                                      ( PARTITION QUAT_1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','DD-MON-YYYY')),
                                       PARTITION QUAT_2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')),
                                       PARTITION QUAT_3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','DD-MON-YYYY')),
                                       PARTITION QUAT_4_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','DD-MON-YYYY')));
                      So QUAT_4_2012 is the 'Last' (though it is not the 'oldest' in terms of the date range it covers) and cannot be deleted. That is why I was able to delete the QUAT_1, 2 and 3 partitions.

                      Would not have suspected that.