?
Solved

Disabling constraints

Posted on 2004-11-26
2
Medium Priority
?
1,265 Views
Last Modified: 2008-01-09
To speed up input performance, I set a primary key index to unusable( and altered session to skip unusable indexes). When attempting to insert into the table, I received an error message of index 'index_name' or partition of such index is in unusable state. I think this is because there is a primary key constraint using this index that is still enabled. First question is "Is that the problem?" and the second question is "How do I disable the constraint?"
0
Comment
Question by:awking00
2 Comments
 
LVL 2

Accepted Solution

by:
Tony_Hasler earned 375 total points
ID: 12682441
As the Oracle documentation states:

ORA-01502 index 'string.string' or partition of such index is in unusable state

Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation.

Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition.

Next time, just disable the primary key constraint.  This will drop the index.  When you enable the constraint again the index will be rebuilt.  For example:

ALTER TABLE xxx DISABLE PRIMARY KEY ;

ALTER TABLE xxx ENABLE PRIMARY KEY USING INDEX TABLESPACE USER_INDEX STORAGE (INITIAL 2M NEXt 2M) ;
0
 
LVL 32

Author Comment

by:awking00
ID: 12739948
Thanks, Tony.
This works fine, but it should be noted that when the primary key is disabled, the index on which it was built is dropped. Also, syntax for enabling is a little different for partitioned indexes, but I figured it out.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question