nologging in insert generating ORA-01502
chijarJan 5 2012 — edited Jan 5 2012dear 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.