We are executing a large number of INSERT operations (140k circa) on a table during an ETL process and this error pops out:

Caught SQLException for insert into DMALM_USER_ROLES [...]
Caused by: java.sql.SQLException: ORA-01502: index 'DM_ALM.DM_ALM_USER_ROLES_PK' or partition of such index is in unusable state

The DBMS is Oracle Database 11g Enterprise Edition Release - 64bit Production

Any hint?

edit: after this process, the table DMALM_USER_ROLES is empty

  • 2
    Did you even try to solve this problem yourself before asking here? I found lots of tips for solving the problem by Googling "ORA-01502". – Colin 't Hart Jan 29 '14 at 10:39
  • 1
    @Colin'tHart The world is getting lazier... – Philᵀᴹ Jan 29 '14 at 10:47
  • Of course I looked for some solutions on the net, and I found the "SKIP_UNUSABLE_INDEX = true" one. But my problem is, the index is a PK. – Filippo Jan 29 '14 at 11:13
  • May the error be generated by the whole bulk load operation being a transaction? – Filippo Jan 29 '14 at 11:18
  • If you "bulk" loaded the data using SQLLoader in direct mode, then this is expected. SQLLoader disables those indexes in order to be faster (a corresponding message is in the log file written by SQL*Loader). You need to re-build the index manually – a_horse_with_no_name Jan 29 '14 at 11:19

You can rebuild an unusable index using

alter index DM_ALM.DM_ALM_USER_ROLES_PK rebuild;
| improve this answer | |
  • You mean, after every bulk load operation? – Filippo Jan 29 '14 at 11:03
  • That depends: What is your bulk loading strategy? Is some step of the ETL process marking the index as unusable, either explicitly or as a side effect of a direct path load for example? – Colin 't Hart Jan 29 '14 at 11:11
  • No, I never mark the index as Unusable. It became so "by itself". – Filippo Jan 29 '14 at 11:16
  • Right, I'm guessing by some direct-path operation. Then yes, you should add the step to rebuild the index after each bulk load operation. – Colin 't Hart Jan 29 '14 at 11:44

Another good practice, would be to drop the index before inserting the bulk data, and then recreating it. It could save you some time. Inserting data with the index attached, may increase the insert-time per record, up to 3 times.

| improve this answer | |
  • So the steps would be: 1. drop index 2. insert * 140k 3. recreate index ? – Filippo Jan 29 '14 at 11:19
  • The ability to make an index "unusable" and then rebuild it after the bulk operation was added for exactly this reason. The big disadvantage to dropping and recreating indexes is that you have to keep the DDL for recreating the indexes in sync with what is actually wanted. If the DBA wants to tweak an index for some reason, they have to remember to change both places. I'd much rather go for the unusable/rebuild approach. See also community.oracle.com/thread/507254 – Colin 't Hart Jan 29 '14 at 11:40
  • Colin, what you say is undoubtfully correct, but what about the segment space manager. Wouldn't recreating the index from scratch (possible after a shrink of the table - if the insert has been performed with APPEND for instante), will reduce the internal fragmentation inside the segment? From what I know, rebuilding the index is done in-place. Thank you. – ddaniel Jan 30 '14 at 7:54
  • As I understand it the index is completely rebuilt in a new place. Segment space fragmentation may still be an issue but I think that's better solved by switching to Automatic Segment Space Management in a locally managed tablespace -- that was new in 9i so if you aren't using this yet you really should be. – Colin 't Hart Jan 30 '14 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.