1 2 Previous Next 23 Replies Latest reply: Feb 15, 2013 8:28 PM by user12241421 Go to original post RSS
      • 15. Re: Creating range paritions automatically
        onedbguru
        I have a function that will tell me the actual name of the partition, and it was very easy to create. (unfortunately it is in an environment where I cannot provide it here). Basically it DOES parse the HIGH_VALUE (long) in dba/user_tab_partitions. When you do something that requires hundreds of partitions and not knowing when you will need that partition, this is a GREAT improvement over trying to guess when I might need a particular "range". There was an enhancement request for interval partitions feature for a partition "window". When it would automagically add a new partition, I would like to see a "window" (eg 5yr moving window for example for DW-type environments) - add new partition, drop old partition probably will never see it, but it was requested :)

        As for the partition management, yes it is different and parsing the LONG is really not that difficult. Once you spend very little time creating the procedures and scripts for this new paradigm, it is all downhill from there. Personally I value my free/off time and hate getting called in because a procedure failed to create the "next" partition(s). This eliminates that problem all together.

        At some point you spent a LOT of time figuring out how write scripts and procedures on how to stop loading/updating long enough so you add the new partitions. You would do the same (create scripts, procedures) with interval partitions and once it was completed, you would not have to worry about it any more. Especially if you think of partitioning as a LOGICAL division, not a PHYSICAL division. ASM + BIG FILE TS + sufficient storage = more sleep!!!!

        As for incorrect dates - hopefully you know your data and your application and how it determines/creates/inserts dates. Since there is typically a "boot" partition that should theoretically not ever have data (for low values) and a review of partitions with dates too far in the future, it should be fairly easy to report on.

        And yes, archaic as in been there, done that, didn't like it, and figured out how to get more sleep!! :) :)

        Edited by: onedbguru on Jan 11, 2013 7:42 AM
        • 16. Re: Creating range paritions automatically
          user12241421
          I need to implement this on an existing table which is already range partitioned, so I tried adding a virtual column to the timestamp field to an existing table and it worked. After that when I tried to convert that to interval partition that again throwed the same error, I think that's becuase it's still referring to the old timestamp field. Should I change my statement of interval partitioning to use the new datefield or should I recreate the table again ?
          SQL> alter table TEST add CONFLICT_TIMEDATE GENERATED ALWAYS AS (cast(CONFLICT_TIME as date)) VIRTUAL;
          
          Table altered.
          
          SQL> alter table TEST set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));
          alter table inimma.EXCEP set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
          *
          ERROR at line 1:
          ORA-14751: Invalid data type for partitioning column of an interval partitioned table
          • 17. Re: Creating range paritions automatically
            rp0428
            >
            I need to implement this on an existing table which is already range partitioned
            >
            You can't change the partitioning of an existing table. You need to recreate the table. You can create a new table and use EXCHANGE PARTITION to move the data.

            There isn't any way to help you with specifics because you keep telling us different tings for what datatype it is you are really using.

            Your original post said you were using a TIMESTAMP datatype
            >
            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.
            >
            The code sample you first posted used TIMESTAMP
            v_limit TIMESTAMP;
            Then when we showed you that you can use TIMESTAMP columns for interval partitioning you suddenly decided your coluimn has a TIME ZONE
            >
            My partitioned column is not DATA or NUMBER its TIMESTAMP (6) WITH LOCAL TIME ZONE.
            >
            And you keep changing the name of the table and columns that you are using.

            Make up your mind and post the DDL for the current partitioned table.

            This is it for me. This code works and uses EXCHANGE PARTITION to move the data from a table that is MANUALLY range partitioned on TIME ZONE column to a new table that is INTERVAL range partitioned on a VIRTUAL column based on the TIME ZONE column.
            -- original table manually range partitioned on a TIMESTAMPE WITH LOCAL TIME ZONE column
            CREATE TABLE Part_timezone
            (
                USERID                 NUMBER,
                ENTRYCREATEDZONE     TIMESTAMP(6) with local time zone
            )
            PARTITION BY RANGE (ENTRYCREATEDZONE)
            ( 
                PARTITION P0 VALUES LESS THAN (TO_TIMESTAMP_TZ('2013-01-01 11:00:00-08:00','YYYY-MM-DDHH:MI:SSTZH:TZM'))
            )
            
            
            -- new INTERVAL partitioned table using a new VIRTUAL column
            CREATE TABLE Part_timezone_virtual
            (
                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'))
            )
            
            -- new work table to support the EXCHANGE PARTITION
            CREATE TABLE Part_timezone_work
            (
                USERID                 NUMBER,
                ENTRYCREATEDZONE     TIMESTAMP(6) with local time zone
            )
            
            -- insert a record into the original table 
            insert into part_timezone values (1, systimestamp - 20)
            
            -- swap the original partition with the work table
            alter table part_timezone exchange partition p0 with table part_timezone_work
            
            -- swap the work table data into the partition of the new table
            alter table part_timezone_virtual exchange partition p0 with table part_timezone_work
            
            -- check that record is now gone from the original table
            select * from part_timezone
            
            -- no records
            
            -- check that new table has the record
            select * from part_timezone_virtual
            
            USERID     ENTRYCREATEDZONE     ENTRYCREATEDATE
            1     12/22/2012 1:56:01.000000 PM     12/22/2012 1:56:01 PM
            • 18. Re: Creating range paritions automatically
              user12241421
              Apologies, my table partitioned column is TIMESTAMP with LOCAL TIME ZONE. I thought this value can be stored in the variable of type timestamp.
              • 19. Re: Creating range paritions automatically
                user12241421
                rp0428,

                Sorry for replying late.

                I tried the scenario you mentioned and that worked for one partition, but if I have to move more than one partition in my old table to new table, then from second partition it's throwing an error parititon doesn't exist, as there is no extra partition created in the new table and I tried to add a parition manullay to interval partitioned table and that doesn't work. Is there any workaround for this.
                SQL> CREATE TABLE Part_timezone_new
                  2  (
                  3      USERID                 NUMBER,
                    ENTRYCREATEDZONE     TIMESTAMP(6) with local time zone
                )  4
                PARTITION BY RANGE (ENTRYCREATEDZONE)
                (   5    6    7
                  8      PARTITION P0 VALUES LESS THAN (TIMESTAMP '2012-07-01 00:00:00.000000000+00:00')
                  9  );
                
                Table created.
                
                SQL>
                SQL> ALTER TABLE Part_timezone_new ADD PARTITION p1 VALUES LESS THAN (TIMESTAMP '2012-08-01 00:00:00.000000000+00:00');
                
                Table altered.
                
                SQL> ALTER TABLE Part_timezone_new ADD PARTITION p2 VALUES LESS THAN (TIMESTAMP '2012-09-01 00:00:00.000000000+00:00');
                
                Table altered.
                
                SQL> ALTER TABLE Part_timezone_new ADD PARTITION p3 VALUES LESS THAN (TIMESTAMP '2012-10-01 00:00:00.000000000+00:00');
                
                Table altered.
                
                SQL> insert into part_timezone_new values (1, systimestamp - 270);
                
                1 row created.
                
                SQL> insert into part_timezone_new values (1, systimestamp - 240);
                
                1 row created.
                
                SQL> insert into part_timezone_new values (1, systimestamp - 210);
                
                1 row created.
                
                SQL> insert into part_timezone_new values (1, systimestamp - 150);
                
                1 row created.
                
                
                SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,INTERVAL from dba_tab_partitions where TABLE_NAME='PART_TIMEZONE_NEW';
                
                TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                               PARTITION_POSITION INT
                ------------------------------ ------------------------------ ---------------------------------------- ------------------ ---
                PART_TIMEZONE_NEW              P0                             TIMESTAMP' 2012-07-01 00:00:00.000000000                  1 NO
                                                                              +00:00'
                
                PART_TIMEZONE_NEW              P1                             TIMESTAMP' 2012-08-01 00:00:00.000000000                  2 NO
                                                                              +00:00'
                
                PART_TIMEZONE_NEW              P2                             TIMESTAMP' 2012-09-01 00:00:00.000000000                  3 NO
                                                                              +00:00'
                
                PART_TIMEZONE_NEW              P3                             TIMESTAMP' 2012-10-01 00:00:00.000000000                  4 NO
                                                                              +00:00'
                
                SQL> CREATE TABLE Part_timezone_virtual_new
                  2  (
                  3      USERID                 NUMBER,
                  4      ENTRYCREATEDZONE     TIMESTAMP(6) with local time zone,
                  5      ENTRYCREATEDATE DATE GENERATED ALWAYS AS (cast(ENTRYCREATEDZONE as date)) VIRTUAL
                  6  )
                  7  PARTITION BY RANGE (ENTRYCREATEDATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
                  8  (
                  9      PARTITION P0 VALUES LESS THAN (TO_DATE('1-7-2012', 'DD-MM-YYYY'))
                 10  )
                 11  ;
                
                Table created.
                
                SQL> CREATE TABLE Part_timezone_work_new
                (
                    USERID                 NUMBER,
                    ENTRYCREATEDZONE     TIMESTAMP(6) with local time zone  
                  5  );
                
                Table created.
                
                SQL> alter table part_timezone_new exchange partition p0 with table part_timezone_work_new;
                
                Table altered.
                
                
                SQL> alter table part_timezone_virtual_new exchange partition p0 with table part_timezone_work_new;
                
                Table altered.
                
                SQL> alter table part_timezone_new exchange partition p1 with table part_timezone_work_new;
                
                Table altered.
                
                SQL>  alter table part_timezone_virtual_new exchange partition p1 with table part_timezone_work_new;
                 alter table part_timezone_virtual_new exchange partition p1 with table part_timezone_work_new
                                                                          *
                ERROR at line 1:
                ORA-02149: Specified partition does not exist
                • 20. Re: Creating range paritions automatically
                  rp0428
                  >
                  I tried the scenario you mentioned and that worked for one partition, but if I have to move more than one partition in my old table to new table, then from second partition it's throwing an error parititon doesn't exist, as there is no extra partition created in the new table and I tried to add a parition manullay to interval partitioned table and that doesn't work. Is there any workaround for this.
                  >
                  The best solution is to create in advance the static partitions that you know you will need for the new table; not just the one partition your example uses. So your example CREATE TABLE statement only created one static partition
                  PARTITION P0 VALUES LESS THAN (TIMESTAMP '2012-07-01 00:00:00.000000000+00:00')
                  Modify that CREATE TABLE statement to create ALL of the partitions ahead of time that you know you have data for. Then the exchange partition will work for all of those partitions.

                  If you can't recreate the new table with the right static partitions then you have to add the new partitions by issuing a 'lock' statement. That statement will force Oracle to actually create the partition specified. See this complete example.
                  http://prutser.wordpress.com/2010/01/11/dropping-interval-partitions/
                  >
                  However, in order to support partition exchange, a workaround is available using the “lock partition” command using the new “partition for ()” clause. To add the partition for July 2010 we use:

                  SQL> lock table ipart partition

                  2 for (to_date('01-jul-2010','DD-MON-YYYy')) in share mode;

                  Table(s) Locked.
                  • 21. Re: Creating range paritions automatically
                    user12241421
                    rp0428,

                    Thanks a lot that worked, I created needed paritions and exchanged them. One last thing is, If I see from a developer perspective, at the end data is in new table, that means code need to be changed to access the table with new name, so I tired dropping the old table after data is moved and renaming the new table with old table, even that worked, but after that am not able to insert any records into the new table, not sure why its behaving like that, I see the table has a virtual columns in it. Should I change my insert statements? Can you please advise.
                    -- Original table manually range partitioned on a TIMESTAMPE WITH LOCAL TIME ZONE column
                    
                    SQL> CREATE TABLE Part_timezone_new1
                       (
                            USERID                 NUMBER,
                            ENTRYCREATEDZONE     TIMESTAMP(6) with local time zone
                    )  
                    PARTITION BY RANGE (ENTRYCREATEDZONE)
                    (   PARTITION P0 VALUES LESS THAN (TIMESTAMP '2012-07-01 00:00:00.000000000+00:00'),
                        PARTITION P1 VALUES LESS THAN (TIMESTAMP '2012-08-01 00:00:00.000000000+00:00'),
                        PARTITION P2 VALUES LESS THAN (TIMESTAMP '2012-09-01 00:00:00.000000000+00:00'),
                        PARTITION P3 VALUES LESS THAN (TIMESTAMP '2012-10-01 00:00:00.000000000+00:00')
                     );
                     
                    Table created.
                     
                     
                    SQL> insert into Part_timezone_new1 values (1, systimestamp - 270);
                     
                    1 row created.
                     
                    SQL> insert into Part_timezone_new1 values (1, systimestamp - 240);
                     
                    1 row created.
                     
                    SQL> insert into Part_timezone_new1 values (1, systimestamp - 210);
                     
                    1 row created.
                     
                    SQL> insert into Part_timezone_new1 values (1, systimestamp - 150);
                     
                    1 row created.
                     
                     
                    SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,INTERVAL from dba_tab_partitions where TABLE_NAME='PART_TIMEZONE_NEW1';
                     
                    TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                               PARTITION_POSITION INT
                    ------------------------------ ------------------------------ ---------------------------------------- ------------------ ---
                    PART_TIMEZONE_NEW              P0                             TIMESTAMP' 2012-07-01 00:00:00.000000000                  1 NO
                                                                                  +00:00'
                     
                    PART_TIMEZONE_NEW              P1                             TIMESTAMP' 2012-08-01 00:00:00.000000000                  2 NO
                                                                                  +00:00'
                     
                    PART_TIMEZONE_NEW              P2                             TIMESTAMP' 2012-09-01 00:00:00.000000000                  3 NO
                                                                                  +00:00'
                     
                    PART_TIMEZONE_NEW              P3                             TIMESTAMP' 2012-10-01 00:00:00.000000000                  4 NO
                                                                                  +00:00'
                     
                    -- New INTERVAL partitioned table using a new VIRTUAL column
                    
                    
                    SQL> CREATE TABLE Part_timezone_virtual_new12
                        (
                            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-7-2012', 'DD-MM-YYYY')),
                             PARTITION P1 VALUES LESS THAN (TO_DATE('1-8-2012', 'DD-MM-YYYY')),
                             PARTITION P2 VALUES LESS THAN (TO_DATE('1-9-2012', 'DD-MM-YYYY')),
                             PARTITION P3 VALUES LESS THAN (TO_DATE('1-10-2012', 'DD-MM-YYYY'))
                      )
                      ;
                     
                    Table created.
                    
                    
                    -- New work table to support the EXCHANGE PARTITION
                    
                     
                    SQL> CREATE TABLE Part_timezone_work_new1
                    (
                        USERID                 NUMBER,
                        ENTRYCREATEDZONE     TIMESTAMP(6) with local time zone  
                    );
                     
                    Table created.
                    
                    
                    -- Swap the original partition with the work table
                    
                     
                    SQL> alter table Part_timezone_new1 exchange partition p0 with table part_timezone_work_new1;
                     
                    Table altered.
                    
                    
                    -- Swap the work table data into the partition of the new table
                     
                    SQL> alter table part_timezone_virtual_new1 exchange partition p0 with table part_timezone_work_new1;
                     
                    Table altered.
                    
                    
                    -- Repeat the same for all parititons
                     
                    SQL> alter table Part_timezone_new1 exchange partition p1 with table part_timezone_work_new1;
                     
                    Table altered.
                     
                    SQL>  alter table part_timezone_virtual_new1 exchange partition p1 with table part_timezone_work_new1;
                    
                     
                    SQL> alter table Part_timezone_new1 exchange partition p2 with table part_timezone_work_new1;
                     
                    Table altered.
                    
                    
                    SQL> alter table part_timezone_virtual_new1 exchange partition p2 with table part_timezone_work_new1;
                     
                    Table altered.
                    
                    
                     
                    SQL> alter table Part_timezone_new1 exchange partition p3 with table part_timezone_work_new1;
                     
                    Table altered.
                    
                    SQL> alter table part_timezone_virtual_new1 exchange partition p3 with table part_timezone_work_new1;
                     
                    Table altered.
                    
                    
                    --- Check all the new records are transferred from Old table to new table
                    
                    
                    SQL>  select count(*) from Part_timezone_new1;
                    
                      COUNT(*)
                    ----------
                             0
                    
                    SQL> select count(*) from Part_timezone_virtual_new1;
                    
                      COUNT(*)
                    ----------
                             4
                    
                    SQL> select count(*) from part_timezone_work_new1;
                    
                      COUNT(*)
                    ----------
                             0
                    
                    --- Drop the old table after all the records are transferred to new table
                    
                    drop table Part_timezone_new1;
                    
                    --- Rename the new table to old table
                    
                    alter table Part_timezone_virtual_new1 rename to Part_timezone_new1;
                    
                    
                    --- Check the count in the renamed table
                    
                    SQL> select count(*) from Part_timezone_new1;
                    
                      COUNT(*)
                    ----------
                             4
                    
                    -- Check if you are able to insert records for future date
                    
                    SQL> insert into PART_TIMEZONE_NEW1 values (1,systimestamp + 30);
                    insert into PART_TIMEZONE_NEW1 values (1,systimestamp + 30)
                                *
                    ERROR at line 1:
                    ORA-00947: not enough values
                    
                    
                    SQL>  insert into PART_TIMEZONE_NEW1 values (1,systimestamp + 30,'13-MAR-2013');
                     insert into PART_TIMEZONE_NEW1 values (1,systimestamp + 30,'13-MAR-2013')
                                 *
                    ERROR at line 1:
                    ORA-54013: INSERT operation disallowed on virtual columns
                    Edited by: user12241421 on Feb 16, 2013 3:45 AM
                    • 22. Re: Creating range paritions automatically
                      rp0428
                      >
                      Thanks a lot that worked, I created needed paritions and exchanged them. One last thing is, If I see from a developer perspective, at the end data is in new table, that means code need to be changed to access the table with new name, so I tired dropping the old table after data is moved and renaming the new table with old table, even that worked, but after that am not able to insert any records into the new table, not sure why its behaving like that, I see the table has a virtual columns in it. Should I change my insert statements?
                      >
                      Unfortunately that is one of the quirks with virtual columns. You can't insert into them but Oracle still includes them in the master list so this won't work:
                      insert into PART_TIMEZONE_NEW1 values (1,systimestamp + 30);
                      You have to specify the column list
                      insert into PART_TIMEZONE_NEW1 (userid, entrycreatedzone) values (1,systimestamp + 30);
                      Of course you should always specify a column list anyway but I wish they would fix this so that Oracle doesn't consider virtual columns when checking the number of values.

                      Don't forget to mark your question ANSWERED if it has been.
                      • 23. Re: Creating range paritions automatically
                        user12241421
                        rp0428,

                        Thanks a lot for helping me till end to resolve this.
                        1 2 Previous Next