This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Feb 15, 2013 6:28 PM by 735446 RSS

Creating range paritions automatically

735446 Newbie
Currently Being Moderated
Oracle Environment : 11.2.0.2

I'm trying to create below procedure to create range partitions automatically every month. I can't implement interval partition here as the field on which the column is partitioned is a Timestamp field. In the below proc I want to select timestamp and pass that to v_limit variable as the partitioned column is timestamp filed. I tried current_timestamp function but thats not returning timestamp value. Is there a way where I can pass timestamp value and add 2 months to it as below (like add 2 months to sysdate)

Didn't work:
SQL> select add_months(current_timestamp,2) from dual;

ADD_MONTH
---------
10-MAR-13

create or replace procedure abc.month_part as

  v_part_name all_tab_partitions.partition_name%type;
  v_limit TIMESTAMP;

begin

  select to_char(trunc(add_months(sysdate,1),'MM'),'MonYYYY')
    into v_part_name 
    from dual; 

  select to_char(trunc(add_months(sysdate,2),'MM'),'dd-mon-yyyy')
    into v_limit
    from dual; 

execute immediate 'ALTER TABLE abc.partxy ADD PARTITION ' || v_part_name || ' VALUES LESS THAN (' || v_limit || ') tablespace USERS';

end;
/
Can someone help me with this.

Thanks in advance
  • 1. Re: Creating range paritions automatically
    sb92075 Guru
    Currently Being Moderated
    user12241421 wrote:
    Oracle Environment : 11.2.0.2

    I'm trying to create below procedure to create range partitions automatically every month. I can't implement interval partition here as the field on which the column is partitioned is a Timestamp field. In the below proc I want to select timestamp and pass that to v_limit variable as the partitioned column is timestamp filed. I tried current_timestamp function but thats not returning timestamp value. Is there a way where I can pass timestamp value and add 2 months to it as below (like add 2 months to sysdate)

    Didn't work:
    SQL> select add_months(current_timestamp,2) from dual;

    ADD_MONTH
    ---------
    10-MAR-13


    Code:
    {
    create or replace procedure abc.month_part as

    v_part_name all_tab_partitions.partition_name%type;
    v_limit TIMESTAMP;

    begin

    select to_char(trunc(add_months(sysdate,1),'MM'),'MonYYYY')
    into v_part_name
    from dual;

    select to_char(trunc(add_months(sysdate,2),'MM'),'dd-mon-yyyy')
    into v_limit
    from dual;

    execute immediate 'ALTER TABLE abc.partxy ADD PARTITION ' || v_part_name || ' VALUES LESS THAN (' || v_limit || ') tablespace USERS';

    end;
    /
    }

    Can someone help me with this.

    Thanks in advance
    see a picture of my car that doesn't
    tell me how to make my car go

    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Creating range paritions automatically
    user503635 Explorer
    Currently Being Moderated
    Since you are using Oracle 11g, no need such trouble, just create interval-partitioned table, it will automatically add one month partition for you whenever needed.

    Add the following clause to you partitioned table creation script.
    INTERVAL (NUMTOYMINTERVAL(1,'month'))

    System will auto generate the partition name following its internal naming convention. If you don't like its name, you may use following command to rename:

    ALTER TABLE partxy RENAME PARTITION sys_p11 TO p_FEB_2012.

    Edited by: user503635 on Jan 10, 2013 5:06 PM

    Edited by: user503635 on Jan 10, 2013 5:07 PM
  • 3. Re: Creating range paritions automatically
    onedbguru Pro
    Currently Being Moderated
    Rather than using this archaic method, look up INTERVAL PARTITIONS. This feature must use a DATE or a NUMBER column type but it looks like you are using a date, so you should be good.

    Interval partitions will automagically be created when data is inserted. I have even tested it to where a monthly partition was created for a month 12 months from now and when data for months 1-11 came along, it will create those partitions on the fly.
  • 4. Re: Creating range paritions automatically
    rp0428 Guru
    Currently Being Moderated
    >
    I'm trying to create below procedure to create range partitions automatically every month. I can't implement interval partition here as the field on which the column is partitioned is a Timestamp field.
    >
    Sure you can - this code works.
    CREATE TABLE TEST1
    (
        USERID                 NUMBER,
        ENTRYCREATEDDATE     TIMESTAMP
    )
    PARTITION BY RANGE (ENTRYCREATEDDATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    ( 
        PARTITION P0 VALUES LESS THAN (TO_DATE('1-1-2013', 'DD-MM-YYYY'))
    )
  • 5. Re: Creating range paritions automatically
    735446 Newbie
    Currently Being Moderated
    sb92075 : thanks, changed it
  • 6. Re: Creating range paritions automatically
    735446 Newbie
    Currently Being Moderated
    user503635 : Enthough it's 11g, I cant implement interval partioning on this table as the column on which the partitioning is done is a Timestamp field and not a date or number field. I tried and get the below error.

    ORA-14751: Invalid data type for partitioning column of an interval partitioned table

    onedbguru : My partitioned column is not DATA or NUMBER its TIMESTAMP (6) WITH LOCAL TIME ZONE.

    rp0428 : When I try to create the table like below, I get this error
    SQL> create table test2
      2  (
      3  USERID                 NUMBER,
      4  ENTRYCREATEDDATE TIMESTAMP (6) WITH LOCAL TIME ZONE NOT NULL ENABLE
      5  )
      6  PARTITION BY RANGE (ENTRYCREATEDDATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
      7  (
      8  PARTITION P0 VALUES LESS THAN (TO_DATE('1-1-2013', 'DD-MM-YYYY'))
      9  )
     10  ;
    create table test2
    *
    ERROR at line 1:
    ORA-14751: Invalid data type for partitioning column of an interval partitioned table
    My question is, Is there a way where I can retrieve complete timestamp value using the select and add_month function? I tried below but this returns the date and not timestamp. I want the output to be a timestamp value so that I can pass it as variable in the procedure. Procedure code is above.
    SQL> select add_months(current_timestamp,2) from dual;
  • 7. Re: Creating range paritions automatically
    user503635 Explorer
    Currently Being Moderated
    Not a issue, I do it with timestamp field in my database successfully
    CREATE TABLE MYTST
     (DTP_DIM_ID TIMESTAMP NOT NULL 
     ,MY_COUNT NUMBER(6) NOT NULL
     )
    PARTITION BY RANGE(DTP_DIM_ID)
    INTERVAL (NUMTOYMINTERVAL(1,'month')) 
    STORE IN (MY_DAT_LRG)
    (PARTITION MY_2012 VALUES LESS THAN (TO_DATE('01-NOV-2012', 'DD-MON-YYYY'))
        tablespace MY_DAT_LRG
        )
    /    
    
    
    Table created
    Edited by: user503635 on Jan 10, 2013 9:39 PM

    Edited by: user503635 on Jan 10, 2013 9:39 PM
  • 8. Re: Creating range paritions automatically
    735446 Newbie
    Currently Being Moderated
    It's strange.

    If I add LOCAL TIME ZONE to timestamp it doesn't work. See below. Is this making the big difference?
    SQL> CREATE TABLE MYTST
      2   (DTP_DIM_ID TIMESTAMP NOT NULL
      3   ,MY_COUNT NUMBER(6) NOT NULL
     )
    PARTITION BY RANGE(DTP_DIM_ID)
      4    5    6  INTERVAL (NUMTOYMINTERVAL(1,'month'))
      7  STORE IN (USERS)
      8  (PARTITION MY_2012 VALUES LESS THAN (TO_DATE('01-NOV-2012', 'DD-MON-YYYY'))
      9      tablespace USERS
     10      )
     11  /
    
    Table created.
    SQL> CREATE TABLE MYTST2
      2   (DTP_DIM_ID TIMESTAMP WITH LOCAL TIME ZONE NOT NULL
      3   ,MY_COUNT NUMBER(6) NOT NULL
      4   )
      5  PARTITION BY RANGE(DTP_DIM_ID)
      6  INTERVAL (NUMTOYMINTERVAL(1,'month'))
      7  STORE IN (USERS)
      8  (PARTITION MY_2012 VALUES LESS THAN (TO_DATE('01-NOV-2012', 'DD-MON-YYYY'))
      9      tablespace USERS
     10  )
     11  /
    CREATE TABLE MYTST2
    *
    ERROR at line 1:
    ORA-14751: Invalid data type for partitioning column of an interval partitioned
    table
  • 9. Re: Creating range paritions automatically
    rp0428 Guru
    Currently Being Moderated
    >
    Enthough it's 11g, I cant implement interval partioning on this table as the column on which the partitioning is done is a Timestamp field and not a date or number field.
    >
    Then add a virtual column to the table so you can partition it properly. This also works. The virtual column is just a data dictionary change.
    CREATE TABLE TEST5
    (
        USERID                 NUMBER,
        ENTRYCREATEDZONE     TIMESTAMP(6) with local time zone,
        ENTRYCREATEDATE DATE GENERATED ALWAYS AS (cast(ENTRYCREATEDZONE as date)) VIRTUAL
    )
    PARTITION BY RANGE (ENTRYCREATEDATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    ( 
        PARTITION P0 VALUES LESS THAN (TO_DATE('1-1-2013', 'DD-MM-YYYY'))
    )
  • 10. Re: Creating range paritions automatically
    user503635 Explorer
    Currently Being Moderated
    Should use TIMESTAMP WITH TIME ZONE, however Oracle shows

    ORA-03001: unimplemented feature


    Would suggest to add additional column with DATE type in your table and use it for partition key. You can store your time stamp info in your ENTRYCREATEDDATE field, but not treat it as a partition key.
  • 11. Re: Creating range paritions automatically
    user503635 Explorer
    Currently Being Moderated
    Should use TIMESTAMP WITH TIME ZONE, however Oracle shows

    ORA-03001: unimplemented feature


    Would suggest to add additional column with DATE type in your table and use it for partition key. You can store your time stamp info in your ENTRYCREATEDDATE field, but not treat it as a partition key.
  • 12. Re: Creating range paritions automatically
    rp0428 Guru
    Currently Being Moderated
    See my reply above for the 'virtual' way to do that. ;)
  • 13. Re: Creating range paritions automatically
    user503635 Explorer
    Currently Being Moderated
    Neat !
  • 14. Re: Creating range paritions automatically
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    onedbguru wrote:
    Rather than using this archaic method, look up INTERVAL PARTITIONS. This feature must use a DATE or a NUMBER column type but it looks like you are using a date, so you should be good.

    Interval partitions will automagically be created when data is inserted. I have even tested it to where a monthly partition was created for a month 12 months from now and when data for months 1-11 came along, it will create those partitions on the fly.
    Using range partitions extensively in 11gr2 - and I do not see any reason for wanting to use interval partitions.

    As a general software engineering rule, I prefer the explicit over the implicit. Exceptions to this need sound justification.

    With partitioning - how do you trap errors where an application attempts to insert data with incorrect dates and times? With range partitioning the required partition will be created. No one wiser to what just happened. An error that remains covered and hidden until someone happens to check the partitions and stumble across the odd one with data for dates and times that do not make sense. (have seen this happen in real world production - which would have had serious implications if the table did not refuse to accept data, with a date that cannot ever possible be sane within the context of that data)

    What about partition management? Without a partition naming template to indicate what range a partition covers (enforced by the custom s/w responsible for explicit partition management), one is now forced to read and parse horrible LONGs from the data dictionary to figure out the range covered by a specific partition. What about wanting to do a partition exchange and having to determine the name assigned by Oracle via interval partitioning?

    So no - I will not call the explicit partitioning management method archaic and the interval partition method new and modern.

    For me automated interval partitions, falls into the same type of category as the recycle bin feature... something along the lines of wtf!? :-)
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points