This discussion is archived
8 Replies Latest reply: Dec 31, 2012 5:59 PM by rp0428 RSS

DBMS_REDEFINITION - Partitioned tables?

DBA112 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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