exchange partition inluding indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: exchange partition inluding indexes

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    exchange partition inluding indexes

    Here is my problem:

    drop table t1;
    drop table t2;

    create table T1
    (
    NO1 NUMBER,
    NO2 NUMBER
    )
    partition by range (NO2)
    (
    partition part_1 values less than (2)
    )
    ;

    create index I1 on T1 (NO1, NO2) local;


    create table T2
    (
    NO1 NUMBER,
    NO2 NUMBER
    );

    create index I2 on T2 (NO1, NO2);

    insert into t2 values(1,1);

    alter table t1 exchange partition part_1 with table t2 including indexes with validation;


    select index_name, status from user_indexes where table_name in('T1','T2');

    INDEX_NAME STATUS
    ------------------------------ --------
    I1 N/A
    I2 VALID


    Why is my index on T1 is invalid?



    This is just an example, i have a big table which i use exchange partition and the query than run on my partitioned table it ran fine and uses the indexes. The problem i am having right now is the gathering stats at night:

    ORA-20000: index "MUS_GEN3"."I1" or partition of such index is in unusable state
    *** 2007-04-25 22:05:48.163
    GATHER_STATS_JOB: GATHER_INDEX_STATS('"MUS_GEN3"','"I1"','"PART_1"', ...)
    ORA-20000: index "MUS_GEN3"."I1" or partition of such index is in unusable state


    How do i put that index to a valid status? Without re-creating it of course. The goal of exchange partition was to save time, if i re-create my index, i am doing exchange partition for nothing.

    also, im not able to rebuild it, if that is the solution:

    alter index i1 rebuild;
    alter index i1 rebuild
    *
    ERROR at line 1:
    ORA-14086: a partitioned index may not be rebuilt as a whole


    alter index i1 partition (PART_1) REBUILD;
    alter index i1 partition (PART_1) REBUILD
    *
    ERROR at line 1:
    ORA-14006: invalid partition name


    But part_1 is my partition name:
    select segment_name, segment_type, partition_name from user_segments
    where segment_name in ('I1');
    SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME
    ------------ ------------------
    I1 INDEX PARTITION PART_1

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by steeve123
    Why is my index on T1 is invalid?

    I1 is a partitioned index...
    you want to query dba_ind_partitions table to check status of each partition.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2000
    Posts
    440
    > select index_name, status from user_ind_partitions where index_name = 'I1';

    INDEX_NAME STATUS
    ------------------------------ --------
    I1 USABLE

    Still invalid.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Stop!!!... breath!!!

    Look at what your query returned...

    Usable does not means Invalid... Usable = Good, like... "you can use it"

    By the way... N/A does not means Invalid either
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2000
    Posts
    440
    HAHA!! wow, i got to stop working over time man!

Posting Permissions

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



Click Here to Expand Forum to Full Width