1 2 Previous Next 23 Replies Latest reply: Feb 15, 2013 8:28 PM by user12241421 RSS

    Creating range paritions automatically

    user12241421
      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
          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
            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
              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
                >
                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
                  user12241421
                  sb92075 : thanks, changed it
                  • 6. Re: Creating range paritions automatically
                    user12241421
                    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
                      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
                        user12241421
                        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
                          >
                          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
                            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
                              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
                                See my reply above for the 'virtual' way to do that. ;)
                                • 14. Re: Creating range paritions automatically
                                  Billy~Verreynne
                                  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