8 Replies Latest reply: Dec 31, 2012 7:59 PM by rp0428 RSS

    DBMS_REDEFINITION - Partitioned tables?

    DBA112
      Dear Experts,

      RDBMS - 11.1.0.7, Can you help me understand below. Table is partitioned with sub-partitions defined as well.

      I want to online redefine partitions of the table using "DBMS_REDEFINITION". I have problem understanding whether the interim table must be created non-partitioned or partitioned for my table structure. Please see oracle suggestion below. I'm trying to understand if my case pertains to rule 1 or rule 2.
      Here are the rules for defining the interim table:
      
         1.  If the partition being redefined is a range, hash, or list partition, the interim table must be non-partitioned.
      
         2.  If the partition being redefined is a range partition of a composite range-hash partitioned table, the interim table must be a hash partitioned table. 
              In addition, the partitioning key of the interim table must be identical to the subpartitioning key of the range-hash partitioned table,
              and the number of partitions in the interim table must be identical to the number of subpartitions in the range partition being redefined.
      Edited by: Ora DBA on Dec 11, 2012 1:02 AM
        • 1. Re: DBMS_REDEFINITION - Partitioned tables?
          rp0428
          >
          I'm trying to understand if my case pertains to rule 1 or rule 2.
          >
          Rule 2 - the table DDL you posted partitions by a composite RANGE-HASH.

          Did you try that? Are you having any issues?
          • 2. Re: DBMS_REDEFINITION - Partitioned tables?
            DBA112
            Thanks rp.

            I will try that and keep updates posted or any issues that I might encounter.

            Edited by: Ora DBA on Nov 24, 2012 8:04 AM
            • 3. Re: DBMS_REDEFINITION - Partitioned tables?
              DBA112
              Tried to create interim table as HASH partitioned as per rules suggested in oracle docs and have been encountering below error: Any thoughts:
              ERROR at line 43:
              ORA-14176: this attribute may not be specified for a hash partition
              
               41    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
                PARTITION BY HASH ("I_TRANSACTION_NO")
               42   43   (PARTITION "OUTBOUND5"  VALUES LESS THAN (1)
               44  PCTFREE 10 PCTUSED 40 INITRANS 30 MAXTRANS 255
              Edited by: DBA112 on Dec 31, 2012 5:36 PM
              • 4. Re: DBMS_REDEFINITION - Partitioned tables?
                rp0428
                >
                Tried to create interim table as HASH partitioned as per rules suggested in oracle docs and have been encountering below error: Any thoughts:


                ERROR at line 43:
                ORA-14176: this attribute may not be specified for a hash partition

                41 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
                PARTITION BY HASH ("I_TRANSACTION_NO")
                42 43 (PARTITION "OUTBOUND5" VALUES LESS THAN (1)
                44 PCTFREE 10 PCTUSED 40 INITRANS 30 MAXTRANS 255
                >
                You don't specify partition names and VALUES LESS THAN clauses for HASH partitioned tables.

                This sample works just fine
                CREATE TABLE "I_TRANSACTION_INT"
                (    "I_TRANSACTION_NO" NUMBER(10,0) NOT NULL ENABLE,
                        "I_TRANSMISSION_NO" NUMBER(10,0) NOT NULL ENABLE,
                        "TRANSACTION_CODE" VARCHAR2(30) DEFAULT 'INSERT' NOT NULL ENABLE,
                        "WRITTEN_BY" VARCHAR2(128) NOT NULL ENABLE,
                        "STATUS" VARCHAR2(30) DEFAULT 'FRESH' NOT NULL ENABLE,
                        "ELEMENT_NAME" VARCHAR2(128) NOT NULL ENABLE,
                        "PROCESS_GROUP" VARCHAR2(101),
                        "PROCESS_SEQUENCE_ID" VARCHAR2(101),
                        "OBJECT_GID" VARCHAR2(101),
                        "SENDER_TRANSACTION_ID" VARCHAR2(50),
                        "REFERENCE_TRANSACTION_ID" VARCHAR2(50),
                        "EXTERNAL_STATUS" VARCHAR2(30),
                        "TRANSACTION_TYPE" VARCHAR2(30),
                        "DATA_QUERY_TYPE_GID" VARCHAR2(101),
                        "FIRST_ATTEMPT_DATE" DATE,
                        "LAST_ATTEMPT_DATE" DATE,
                --        "XML_BLOB" CLOB,
                        "PARTITION_KEY" NUMBER NOT NULL ENABLE,
                        "DOMAIN_NAME" VARCHAR2(50) NOT NULL ENABLE,
                        "INSERT_USER" VARCHAR2(128) NOT NULL ENABLE,
                        "INSERT_DATE" DATE NOT NULL ENABLE,
                        "UPDATE_USER" VARCHAR2(128),
                        "UPDATE_DATE" DATE,
                         CONSTRAINT "PK_I_TRANSACTION" PRIMARY KEY ("I_TRANSACTION_NO")
                  USING INDEX PCTFREE 10 INITRANS 30 MAXTRANS 255 COMPUTE STATISTICS
                  )
                  PARTITION BY HASH ("I_TRANSACTION_NO")
                • 5. Re: DBMS_REDEFINITION - Partitioned tables?
                  DBA112
                  rp.. Syntax is correct, but according to the rules to create interim table in case of RANGE-HASH partitioned table, number of partitions in the interim must be equal to number of sub partitions in the source table, which is 16 in this case.

                  According to the syntax you've suggested, it only creates 1 partition in the interim table.
                  • 6. Re: DBMS_REDEFINITION - Partitioned tables?
                    rp0428
                    >
                    According to the syntax you've suggested, it only creates 1 partition in the interim table.
                    >
                    You need to specify the number of hash partitions that you want to create. See the VLDB and Partitioning Guide
                    http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#i1006508
                    >
                    Example 4-5 Creating a hash-partitioned table

                    CREATE TABLE scubagear
                    (id NUMBER,
                    name VARCHAR2 (60))
                    PARTITION BY HASH (id)
                    PARTITIONS 4
                    STORE IN (gear1, gear2, gear3, gear4);
                    • 7. Re: DBMS_REDEFINITION - Partitioned tables?
                      DBA112
                      Thanks rp for redirecting it in the right direction. I will try creating interim as per docs.. will see how it goes.. Truly, appreciate your help.. I couldn't find a lot of info on this anywhere on the internet..
                      • 8. Re: DBMS_REDEFINITION - Partitioned tables?
                        rp0428
                        Oracle-base usually has some pretty good articles but noone will match your exact specs
                        http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php

                        Strip down the DDL and post it if you need someone to try to reproduce the problem. Data isn't needed and most of the table columns aren't needed either.