Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Unanswered: truncate partition-index unusable.

    I have a partitined table (list partitioned) with a primary key defined on the table.
    the table structure is following way..

    CREATE TABLE xyz
    (
    ERS_TX_ID CHAR(14 BYTE) NOT NULL,
    CREATED_BY CHAR(3 BYTE),
    CREATED_BY_MSISDN VARCHAR2(15 BYTE),
    PARTITION_ID NUMBER(2),
    UPDATED_BY_MSISDN VARCHAR2(15 BYTE)
    )
    TABLESPACE ERSDATA
    MAXTRANS 255
    PARTITION BY LIST (PARTITION_ID)
    (PARTITION TRANSD01 VALUES (1)
    TABLESPACE TRANSD01
    ,
    PARTITION TRANSD02 VALUES (2)
    TABLESPACE TRANSD01
    ),
    .
    .
    .
    PARTITION TRANSD23 VALUES (23)
    TABLESPACE TRANSD23
    BUFFER_POOL DEFAULT
    ),
    PARTITION TRANSD24 VALUES (24)
    TABLESPACE TRANSD24
    ;
    CREATE UNIQUE INDEX PK_TX_DISTRIBUTOR_DEALER ON TX_DISTRIBUTOR_DEALER
    (ERS_TX_ID);

    ALTER TABLE TX_DISTRIBUTOR_DEALER ADD (
    CONSTRAINT PK_TX_DISTRIBUTOR_DEALER PRIMARY KEY (ERS_TX_ID)
    USING INDEX
    TABLESPACE ERSINDX;

    After all the partitions 1..24 are filled I reuse the same partitions to store data ..such that..

    export the partition data..,
    truncate the partition.

    but once I truncate the index goes to unusable and the DML cannot be performed on that table.

    If I rebuild the index everything works fine.
    But rebuilding indexes taking lots of time and lots of TEMP tablespace space.

    Is there any alternative left for this issue.

    Thanks & Regards,
    sridhar

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    For 10g you can use the UPDATE INDEXES clause:
    Code:
    ALTER TABLE MyTable TRUNCATE PARTITION ThePartition
           UPDATE {GLOBAL} INDEXES;
    For 8i and 9i you have to re-build indexes.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If I remember correctly you can do it in 9i using 'alter table...exchange partition' to exchange the partition in question with an empty table of the same format. We use this method to reorg individual partitions on a live system. Not sure of the effect on a global partitioned index though.

    Alan

  4. #4
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89
    Quote Originally Posted by LKBrwn_DBA

    For 10g you can use the UPDATE INDEXES clause:
    Code:
    ALTER TABLE MyTable TRUNCATE PARTITION ThePartition
           UPDATE {GLOBAL} INDEXES;
    For 8i and 9i you have to re-build indexes.

    Hi ,
    Thank you very much for the answer.But I tried the same option on my database(9.2.0.6) it worked.!!!!!!!
    Let me know if you have any remarks otherwise I consider my problem is solved.
    Thanks once again.

    cheers,
    sri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •