| index 'index-name' or partition of such index is in unusable state [message #550548] |
Tue, 10 April 2012 05:39  |
prejib
Messages: 126
Registered: March 2009
Location: India
|
Senior Member |
|
|
Hi,
We are getting the below error frequently from the application while doing insertion/dataloading to a table. The mentioned error is in the Primary key index
Error
-----
'ORA-01502: index 'INDEX_NAME' or partition of such index is in unusable state'.
I set the value SKIP_UNUSABLE_INDEXES = TRUE using the command 'ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = TRUE' to avoid this. Again we are getting the same error and Every time Iam rebuilding('alter index INDEX_NAME rebuild') the index and doing the DML Operation. Can you please provide a permenant solution for this ?
Regards,
Prejib
|
|
|
|
|
|
|
|
|
|
| Re: index 'index-name' or partition of such index is in unusable state [message #550554 is a reply to message #550552] |
Tue, 10 April 2012 06:05  |
prejib
Messages: 126
Registered: March 2009
Location: India
|
Senior Member |
|
|
Hi,
In the oracle Enterprise manager , I can see 2 reccomendation for the same table to reclaim the space as given in the attachment and I have executed the below statement to compress the space .
alter table "TABLE_NAME" compress for oltp;
alter table "TABLE_NAME" move;
Please let me know what are the other details you want ?
Regards,
Prejib
|
|
|
|
|
|
|
|
|
|
|
|
| Re: index 'index-name' or partition of such index is in unusable state [message #550570 is a reply to message #550567] |
Tue, 10 April 2012 07:45 |
John Watson
Messages: 8988
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
If you don't buy the licence, you can't use OLTP compression. But why do you want to use it? It seems that that you have deliberately broken all your indexes by compressing and moving the tables, because Enterprise Manager said it would save you a few MB. Just leave things as they were. And you will have to alter the the table NOCOMPRESS to bring your database back into licence compliance.
|
|
|
|
|
|
| Re: index 'index-name' or partition of such index is in unusable state [message #551454 is a reply to message #551397] |
Wed, 18 April 2012 08:13 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
prejib wrote on Wed, 18 April 2012 00:33 I have alter the table with nocompress option , still getting the same error
This was just to put the database in license compliance.
You're not showing us any queries to prove that you have any errors. All you are telling us is that you have an error. Show us the commands you ran that built the index and ALL output. I will only ask once.
|
|
|
|
| Re: index 'index-name' or partition of such index is in unusable state [message #551532 is a reply to message #551454] |
Thu, 19 April 2012 01:09 |
prejib
Messages: 126
Registered: March 2009
Location: India
|
Senior Member |
|
|
The below given is the error getting from application.
Caused by: java.sql.SQLException: ORA-01502: index 'INDEX_NAME' or partition of such index is in unusable state
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
I always execute the o/p of the below query to rebuild the index.
SQL> select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where STATUS = 'UNUSABLE';
|
|
|
|
|
|
|
|
| Re: index 'index-name' or partition of such index is in unusable state [message #551535 is a reply to message #551534] |
Thu, 19 April 2012 01:42 |
John Watson
Messages: 8988
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Prejib, it is hard to assist because you NEVER give complete information. We need to know the sequence of events that results in the index going bad. Show the complete sequence, like this:orcl> conn scott/tiger
Connected.
Session altered.
orcl> select index_name,status from user_indexes where status <> 'VALID';
no rows selected
orcl> alter table emp move;
Table altered.
orcl> select index_name,status from user_indexes where status <> 'VALID';
INDEX_NAME STATUS
------------------------------ --------
PK_EMP UNUSABLE
orcl> alter index pk_emp rebuild;
Index altered.
orcl> select index_name,status from user_indexes where status <> 'VALID';
no rows selected
orcl> |
|
|
|
| Re: index 'index-name' or partition of such index is in unusable state [message #551538 is a reply to message #551535] |
Thu, 19 April 2012 02:02 |
prejib
Messages: 126
Registered: March 2009
Location: India
|
Senior Member |
|
|
I have changed the exact schema_name and table_name
SQL> show parameter skip;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE
SQL> select index_name,status from dba_indexes where status = 'UNUSABLE';
no rows selected
SQL> alter table "schema_name"."table_name" compress for oltp;
Table altered.
SQL> select index_name,status from dba_indexes where status = 'UNUSABLE';
no rows selected
SQL> alter table "schema_name"."table_name" move;
Table altered.
SQL> select index_name,status from dba_indexes where status = 'UNUSABLE';
INDEX_NAME STATUS
------------------------------ --------
TABLE_NAME_P UNUSABLE
SQL> select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where STATUS = 'UNUSABLE';
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index schema_name.table_name rebuild;
SQL> alter index schema_name.table_name rebuild;
Index altered.
SQL> select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where STATUS = 'UNUSABLE';
no rows selected
SQL> alter table PHILIP_PUB.PH_PRODUCT_FILTER_ATTRIBUTES nocompress;
Table altered.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: index 'index-name' or partition of such index is in unusable state [message #551555 is a reply to message #551543] |
Thu, 19 April 2012 04:58 |
John Watson
Messages: 8988
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
A week ago, Cookie told you to investigate the technique used for loading data. It is probably something like sqlldr with the skip_index_maintenance clause, which would cause this. But since you haven't provided any information, I really don't know.
And btw, I notice that you have never said "thank you for your time". Not even once. Well, I've no more time for this. Perhaps someone else has.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
