-
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
-
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.
-
> select index_name, status from user_ind_partitions where index_name = 'I1';
INDEX_NAME STATUS
------------------------------ --------
I1 USABLE
Still invalid.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|