A reader, April 05, 2002 - 1:57 pm UTC
Thanks for your answer.
You mentioned the status in dba_objects is probably 'obsolete'.
Can you recommend a better way to verify the validity of the objects in the database? It seems you can really only get a true status from dba_objects for things like triggers and procedures...
Also, for trivia's sake, what tables are you querying with your decode statements below?
Thanks again!
April 05, 2002 - 2:39 pm UTC
goto each object type table (dba_tables, dba_indexes) and look there.
The decodes are from the view definitions - select text from all_views where view_name = 'xxxxx';
make sure the set long 50000 in sqlplus first
skip_unusable_indexes=true
A reader, February 19, 2003 - 12:44 pm UTC
Shouldn't skip_unusable_indexes prevent this error ?
SQL> analyze table ism_lat estimate statistics
analyze table ism_lat estimate statistics
*
ERROR at line 1:
ORA-01502: index 'REL.SYS_C001218' or partition of such index is in unusable
state
SQL> alter session set skip_unusable_indexes=true;
Session altered.
SQL> analyze table ism_lat estimate statistics;
analyze table ism_lat estimate statistics
*
ERROR at line 1:
ORA-01502: index 'REL.SYS_C001218' or partition of such index is in unusable
state
February 19, 2003 - 3:34 pm UTC
<quote>
TRUE disables error reporting of indexes and index partitions marked UNUSABLE. This setting allows all operations (inserts, deletes, updates, and selects) on tables with unusable indexes or index partitions.
</quote>
so no, because you are doing an analyze and your analyze is equivalent to
analyze table t estimate statistics
for table
for all indexes
for all columns
you should use dbms_stats (won't fix this issue, but you should) and you should look into whether you want/need/desire the histograms (they are costly to compute) and if not, maybe you can turn that estimate into COMPUTE and get better stats.
Can you give me an example to make an index Invalid ?
David Jiang, June 05, 2003 - 4:49 pm UTC
Tom,
Thank you for the web site, which relly give me a lot of help.
I see that you have a column Status in User_indexes. But it seems to me all indexes is either created valid or not created at all. The index created on the table x is gone if the table x is dropped. It seems that the Status columna is useless here in user_indexes. Is this true?
Please help me to verify it.
Thanks a lot.
June 05, 2003 - 6:30 pm UTC
what about unusable?
or inprogrs?
or N/A?
Excellent
David Jiang, June 06, 2003 - 3:18 pm UTC
Thanks a lot, Tom.
OK, I see it now. Can you provide examples of valid/inprogress/ n/a / usable or unusable regarding index status. I want to know how to make the status showed in all these different condition.
Thanks again for your prompt answer.
June 06, 2003 - 4:07 pm UTC
valid -- just create one
unusable -- alter index iname unusable;
If you do "shutdown abort" while a CREATE INDEX ... indextype ctxsys.ConText job is running (which otherwise would've completed without error) then you can get... STATUS = 'INPROGRS'
local index becomming unusable,
sns, February 21, 2005 - 10:59 am UTC
We have a range partitioned table and couple of local indexes defined on them.
Recently we split the partition at the MAXVALUE for the business reasons.
Two things happened:
The local index got split too (which is good) however, the status became UNUSABLE.
The tablespace used to store new partition of the index was different than other partitions(I wanted to store in the same tablespace like other partitions).
I did a small test by creating a partition table and a local index on that. According to that test, the local index DID NOT became UNUSABLE after I split the table partition and the new partition was stored in the right tablespace.
Why my test table and index behaved differently than my production table and index?
February 21, 2005 - 12:46 pm UTC
so, show us the commands you used to reproduce -- the entire test case.
if the partition has data in it, I would expect the local index to go unusable (until 10g when there is an option to maintain local indexes just like global indexes has in 9i)
sns, February 21, 2005 - 2:01 pm UTC
May be I did not had any values in the MAXVALUE bucket when I split.
I noticed the local index became unusable when I had data in the MAXVALUE bucket.
Is this something inevitable? I am using 10.1.0.3.
The theory is:
Rebuild the local index after I split the table partition.
Rebuild the global partitioned index after the tab partition is split.
Rebuild the non-partitioned index???
Thanks
February 21, 2005 - 3:47 pm UTC
did you use the
UPDATE INDEXES
clause -- if you do, it is not inevitable.
unusable index
sns, February 21, 2005 - 4:52 pm UTC
I did not use UPDATE INDEXES clause.
Well, I am not sure where this UPDATE INDEXES clause comes into picture. Does this apply to all the 3 types of Indexes (local, global partitioned and non-partitioned)?
Is this beneficial than rebuilding the index partition after it becomes UNUSABLE?
Thanks
February 22, 2005 - 8:00 am UTC
if (you do not want to have downtime due to unusable indexes)
then
update indexes will do the inserts/deletes to move stuff around
in the index, causing the ALTER to take longer from the perspective
of the person doing the alter but not causing "downtime" from the
perspective of the people running the queries
else
don't use update indexes and let them go unusable and rebuild them afterwards.
overall this ddl will tend to run faster but you will have time when the data
is basically "not available" if you wanted to access it via those indexes
end if
it is a trade off -- continous availability, vs, raw speed of the operation.
You can always check out the sql reference guide for the syntax, but "update indexes" maintains local and global, consider:
ops$tkyte@ORA10G> CREATE TABLE t
2 (
3 temp_date date,
4 x int,
5 y int
6 )
7 PARTITION BY RANGE (temp_date)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')),
10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')),
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 /
Table created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create index t_idx1 on t(x) global;
Index created.
ops$tkyte@ORA10G> create index t_idx2 on t(y) local;
Index created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> insert into t select to_date('10-mar-2003')+rownum, rownum, rownum from all_users where rownum <= 10;
10 rows created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select index_name, partition_name, status from user_ind_partitions where index_name like 'T_IDX_';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T_IDX2 JUNK USABLE
T_IDX2 PART1 USABLE
T_IDX2 PART2 USABLE
ops$tkyte@ORA10G> select index_name, status from user_indexes where table_name = 'T' ;
INDEX_NAME STATUS
------------------------------ --------
T_IDX1 VALID
T_IDX2 N/A
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> alter table t split partition junk at (to_date('15-mar-2003','dd-mon-yyyy'))
2 into ( partition part3, partition junk);
Table altered.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select index_name, partition_name, status from user_ind_partitions where index_name like 'T_IDX_';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T_IDX2 JUNK UNUSABLE
T_IDX2 PART1 USABLE
T_IDX2 PART2 USABLE
T_IDX2 PART3 UNUSABLE
ops$tkyte@ORA10G> select index_name, status from user_indexes where table_name = 'T' ;
INDEX_NAME STATUS
------------------------------ --------
T_IDX1 UNUSABLE
T_IDX2 N/A
<b>that is what happens when no index maintainence is done. the global goes unusable and the affected partitions for local indexes do as well, but</b>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> alter index t_idx1 rebuild;
Index altered.
ops$tkyte@ORA10G> alter index t_idx2 rebuild partition part3;
Index altered.
ops$tkyte@ORA10G> alter index t_idx2 rebuild partition junk;
Index altered.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select index_name, partition_name, status from user_ind_partitions where index_name like 'T_IDX_';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T_IDX2 JUNK USABLE
T_IDX2 PART1 USABLE
T_IDX2 PART2 USABLE
T_IDX2 PART3 USABLE
ops$tkyte@ORA10G> select index_name, status from user_indexes where table_name = 'T' ;
INDEX_NAME STATUS
------------------------------ --------
T_IDX1 VALID
T_IDX2 N/A
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> alter table t split partition junk at (to_date('16-mar-2003','dd-mon-yyyy'))
2 into ( partition part4, partition junk) <b>update indexes;
</b>
Table altered.
<b>using the new update indexes in 10g, you get all indexes maintained:</b>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select index_name, partition_name, status from user_ind_partitions where index_name like 'T_IDX_';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T_IDX2 JUNK USABLE
T_IDX2 PART1 USABLE
T_IDX2 PART2 USABLE
T_IDX2 PART3 USABLE
T_IDX2 PART4 USABLE
ops$tkyte@ORA10G> select index_name, status from user_indexes where table_name = 'T' ;
INDEX_NAME STATUS
------------------------------ --------
T_IDX1 VALID
T_IDX2 N/A
splitting table with 10g
Miroslav Siket, June 20, 2005 - 3:13 am UTC
Hello Tom,
I am using Oracle 10g and I have big tables with local indexes where the split operation takes several seconds. An example operation would be:
alter table MY_TABLE split partition MAX_VALUE at (123456789) into (partition MY_OLD_DATA, partition MAX_VALUE) UPDATE INDEXES;
I am using UPDATE INDEXES in the split statement as you suggest with 10g, but if at the same time some other application tries to insert data into the given table I get the error:
ORA-01502: index 'MY_INDEX' or partition of such index is in unable state
Of course, when the split finishes, everything is all right, all indexes are usable again, but during the time of split, indexes are unusable. I was able to run the following query:
select * from user_ind_partitions where status != "USABLE"
duting the split and I was able to find index partitions of my table among unusable while the operation lasted.
Isn't that supposed to work transparently for the applications? How can I prevent this situation to occur or how should I go about it? I need to have this database available all the time.
June 20, 2005 - 10:01 am UTC
you are doing DDL to the partition, it is the DDL on the partition that is in fact the "culprit" here. The update indexes is to preserve the ability to read the object. But during the DDL operation, you are not going to modify that partition (it is being modified itself, it is like you are doing a massive update in a way, rewriting the entire partition)
Are you doing this frequently? you could instead not use max value and just add partitions at the end over time maybe?
table split in 10g
Miroslav Siket, June 21, 2005 - 3:03 am UTC
Hello Tom,
I have imagined this would be the cause. In 9i (on another server) I pre-create partitions instead of splitting the MAXVALUE one. There is everything all right as at the time when I add a partition, it is well ahead of time and no application tries to do any operation on it (naturaly). The fields that I have the split to be made on is the unix timestamp and I have the data that are only added (monitoring data). Thus, in 9i I precreate partitions every day (several millions of entries per day) and I compress the old partitions. The current partition (where DMLs are done) is untouched by any DDL. The only disadvantage of this approach is that I have to pre-create enough of partitions ahead of time. If something fails there, no data will be inserted - i.e. when I fail to precreate partitions for some time. On the other hand, with MAXVALUE partitions I thought I could do these kind of operations post mortem (at least in 10g) - i.e. at any time I can schedule that and at no time I would endanger the applications. Would locking of the table for the time of the split allow this?
June 21, 2005 - 4:35 pm UTC
<quote>
Would locking of the table for the time of the split allow this?
</quote>
it would prevent the insertions, just the same case you have now!
A straight question...
A reader, September 02, 2005 - 11:21 am UTC
How can you see if an index is a local or global?
September 03, 2005 - 7:28 am UTC
ops$tkyte@ORA10G> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(25)
6 )
7 PARTITION BY RANGE (dt)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 /
Table created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create index t_local on t(x) local;
Index created.
ops$tkyte@ORA10G> create index t_global on t(y) global
2 partition by range(y)
3 ( partition p1 values less than ( 'X' ),
4 partition p2 values less than (MAXVALUE)
5 );
Index created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select index_name, locality
2 from user_part_indexes
3 where table_name = 'T';
INDEX_NAME LOCALI
------------------------------ ------
T_LOCAL LOCAL
T_GLOBAL GLOBAL
A straight question...
David Martin, September 02, 2005 - 11:22 am UTC
How can you see if an index is a local or global?
Thanks!
Dave, September 05, 2005 - 4:41 am UTC
Useful - to skip unused Index
Surangani, October 07, 2005 - 5:16 am UTC
As a DB user, i was querying some data from a table which ia partitioned.So the query stmt returns error that the index is not in use.After referring this site, I was able to skip the unusable index(skip_unusable_indexes) and then run the query, which retrieved data.
Split partition containing data doesn't always make the index partitions unusable...
Dawn, May 01, 2007 - 12:03 pm UTC
Hi Tom,
We had an error in our production database today whereby two partitions on each of the local binary indexes of a partitioned table were marked as invalid. The reason why is partly to do with not having "update indexes" after the split partition request and partly because there was data in the partition being split, but further investigation proved interesting.
I searched for information on local indexes going unusable following a split, and came up with the following:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#i1008028 "If the partition you are splitting contains any data, indexes may be marked UNUSABLE as explained in the following table..."
Thing is, I know that the (catchall) partition being split every evening contains data quite regularly, but this error doesn't crop up very often! Looking more closely, I found that if the data stayed entirely within one of the two new partitions, the indexes are fine. If, however, the data is moved into both partitions, then the corresponding index partitions become unusable, as this test case shows:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
SQL> CREATE TABLE dawn_test_2 (col1 DATE)
2 PARTITION BY RANGE (col1)
3 (
4 PARTITION PARTNO_01 VALUES LESS THAN (TO_DATE(' 2007-04-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
5 PARTITION PARTNO_OTHER VALUES LESS THAN (MAXVALUE));
Table created.
SQL> CREATE BITMAP INDEX dawn_test2_bind ON DAWN_TEST_2
2 (col1) LOCAL ;
Index created.
SQL> INSERT INTO dawn_test_2 VALUES (TO_DATE('20/04/2007', 'dd/mm/yyyy'));
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM dawn_test_2 PARTITION (partno_other);
COL1
---------
20-APR-07
SQL> ALTER TABLE dawn_test_2 SPLIT PARTITION PARTNO_OTHER AT (TO_DATE('20/04/2007', 'dd/mm/yyyy'))
2 INTO (PARTITION partno_02, PARTITION PARTNO_OTHER);
Table altered.
SQL> SELECT * FROM dawn_test_2 PARTITION (partno_other);
COL1
---------
20-APR-07
SQL> SELECT index_name, partition_name, status FROM user_ind_partitions
2 WHERE index_name = 'DAWN_TEST2_BIND';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
DAWN_TEST2_BIND PARTNO_01 USABLE
DAWN_TEST2_BIND PARTNO_02 USABLE
DAWN_TEST2_BIND PARTNO_OTHER USABLE
SQL> ALTER TABLE dawn_test_2 SPLIT PARTITION PARTNO_OTHER AT (TO_DATE('21/04/2007', 'dd/mm/yyyy'))
2 INTO (PARTITION partno_03, PARTITION PARTNO_OTHER);
Table altered.
SQL> SELECT * FROM dawn_test_2 PARTITION (partno_other);
no rows selected
SQL> SELECT index_name, partition_name, status FROM user_ind_partitions
2 WHERE index_name = 'DAWN_TEST2_BIND';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
DAWN_TEST2_BIND PARTNO_01 USABLE
DAWN_TEST2_BIND PARTNO_02 USABLE
DAWN_TEST2_BIND PARTNO_03 USABLE
DAWN_TEST2_BIND PARTNO_OTHER USABLE
SQL>
SQL> INSERT INTO dawn_test_2 VALUES (TO_DATE('21/04/2007', 'dd/mm/yyyy'));
1 row created.
SQL> INSERT INTO dawn_test_2 VALUES (TO_DATE('22/04/2007', 'dd/mm/yyyy'));
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM dawn_test_2 PARTITION (partno_other);
COL1
---------
21-APR-07
22-APR-07
SQL>
SQL> ALTER TABLE dawn_test_2 SPLIT PARTITION PARTNO_OTHER AT (TO_DATE('22/04/2007', 'dd/mm/yyyy'))
2 INTO (PARTITION partno_04, PARTITION PARTNO_OTHER);
Table altered.
SQL> SELECT * FROM dawn_test_2 PARTITION (partno_other);
COL1
---------
22-APR-07
SQL> SELECT index_name, partition_name, status FROM user_ind_partitions
2 WHERE index_name = 'DAWN_TEST2_BIND';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
DAWN_TEST2_BIND PARTNO_01 USABLE
DAWN_TEST2_BIND PARTNO_02 USABLE
DAWN_TEST2_BIND PARTNO_03 USABLE
DAWN_TEST2_BIND PARTNO_04 UNUSABLE
DAWN_TEST2_BIND PARTNO_OTHER UNUSABLEIs this always the case, or are there times when splitting a partition where the data stays entirely within one of the new partitions cause the local index partitions to go unusable?
Unusable Index
Rajeshwaran, Jeyabal, October 08, 2011 - 11:11 pm UTC
Tom:
I was reading Chapter#2 on Effective Oracle by design
<quote>
If you don’t ever drop the index, but set it to UNUSABLE instead, you can never lose the index. If the command to reenable
the index fails, when the users run queries that need that index, they will get an error message, which, of course,
they will report immediately. So, rather than having the system suffer from mysterious performance issues for hours or days, the DBA will get a call early on and fix the problem
</quote>
rajesh@ORA10GR2> create index t_ind on t(object_id) nologging;
Index created.
Elapsed: 00:00:00.06
rajesh@ORA10GR2> alter index t_ind unusable;
Index altered.
Elapsed: 00:00:00.06
rajesh@ORA10GR2>
rajesh@ORA10GR2> select object_name from t where object_id = 5;
OBJECT_NAME
------------------------------
CLU$
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 168 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 1 | 22 | 168 (2)| 00:00:03 |
--------------------------------------------------------------------------
I don't see any error message while running queries that need an Index. Is that something got changed with Oracle 10G ?
October 09, 2011 - 11:13 am UTC
Things change over time, as has the default setting of
ops$tkyte%ORA11GR2> show parameter skip
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE
it used to be false by default in 9i.
but the advice still stands as it was written. Inserts will fail (as would updates of the indexed columns and deletes) - raising the error. AND the index will be in the dictionary - it will not have gone 'missing', and the DBA can easily see "oh, wait there are unusable indexes" when diagnosing a performance issue.
So, I stick with setting unusable whenever possible. Better to NOT lose the definition of the index.
Indexes become unusable
Jason, August 22, 2012 - 9:31 am UTC
We have a list partitioned table. On the table there 9 non-partitioned indexes.
Periodically all of the indexes to become unusable.
Any documentation I can find states "Almost any partition-level operation on a table will render any globally partitioned or non-partitioned index unusable.".
Can you confirm that one of the "Almost any partition-level operations" is when we add a partition? Would adding rows via SQL Loader also make them unusable?
Thanks
Thanks
August 29, 2012 - 11:23 am UTC
Adding will not
ops$tkyte%ORA11GR2> create table t
2 ( id number,
3 data varchar2(30)
4 )
5 partition by list (id) (
6 partition p1 values (1),
7 partition p2 values (2),
8 partition p3 values (3)
9 )
10 ;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t select mod(rownum,3)+1, 'x' from all_users;
38 rows created.
ops$tkyte%ORA11GR2> create index t_idx on t(data) GLOBAL;
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> alter table t add partition p4 values (4);
Table altered.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select status from user_indexes where index_name = 'T_IDX';
STATUS
--------
VALID
a direct path load could leave them unusable if they are unique and the uniqueness is violated.