CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Pavel Trostianko
Pavel Trostianko

Reputation: 119

Oracle SQL Index rebuild failed with recursive error

Failed to rebuild table index in Oracle SQL Developer.

By the way I'm trying to delete from log table some old logs which are older than 2 years.

DELETE FROM SCHEME.IP_LOG_TABLE WHERE LOG_DATE <= SYSDATE - interval '2' year

I'm getting this error:

Error starting at line : 1 in command -

DELETE FROM SCHEME.IP_LOG_TABLE WHERE LOG_DATE <= SYSDATE - interval '2' year

Error report -

ORA-01502: index 'SCHEME.PK_IP_LOG_ID' or partition of such index is in unusable state

PK_IP_LOG_ID is unique unusable index with normal index_type that is using ID column.

I try to find in Google how to fix this error and i find this:

The ORA-01502 error can be easily fixed by issuing the alter index index_name rebuild partition partition_name;

But the main problem than i try this:

ALTER INDEX PK_IP_LOG_ID REBUILD

I get this error that i didn't understand:

ERROR at line 1: ORA-00604: error occurred at recursive SQL level

Upvotes: 1

Views: 300

Answers (1)

Pavel Trostianko
Pavel Trostianko

Reputation: 119

Oracle was expecting one row but It was getting multiple rows. I truncated table and inserted only row which has one value.

Upvotes: 1

Related Questions