Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

nologging in insert generating ORA-01502

chijarJan 5 2012 — edited Jan 5 2012
dear all,
Facts:
- oracle 10.1 in AIx 5.3.
- My database is a DWH environment.
- Table HD is partitioned, each partition is NOLOGGING and NOCOMPRESS.
- Table HD have a UNIQUE index and is LOCAL index with NOLOGGING too.
- Table HD is loaded through an INSERT statement with NOLOGGING like:

INSERT INTO HD NOLOGGING
(COLUMN1, COLUMN2)
SELECT A.COLUMN1,A.COLUMN2 FROM TP;

and this generating the error:
INSERT
*
ERROR at line 1:
ORA-01502: index 'SCHEMA.HD' or partition of such index is in unusable state.

I read the metalink note : ID 1070139.6 but this show me how can i skip this index. My goals is to know:

1- Why my index is in unusable state ??
i suspect that this is because NOLOGGING in INSERT statement but im not sure.
2- how can i avoid this ORA-01502 error ?? putting /*+ APPEND */ hint instead NOLOGGING in INSERT statement ??

thanks for your answers, and apologize my english, is not very well.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 2 2012
Added on Jan 5 2012
4 comments
240 views