- 13 Sep, 2023
- 0 Comments
- 1 Min Read
How to Resolve ORA-01502 Error
How to Resolve ORA-01502 Error
ORA-01502 means that the index that the optimizer want to use is in UNUSABLE state. so the solution is that you can either rebuild it or work around it.
Solution :
- Rebuild Index :
SQL> ALTER INDEX EMP_SALARY; Index altered. Or rebuild it online: SQL> ALTER INDEX EMP_SALARY ONLINE; Index altered.
2. SKIP_UNUSABLE_INDEXES
SQL> ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE; Session altered.
3. Drop Index to Workaround ORA-01502
First of all, we need to disable the primary key constraint.SQL> ALTER TABLE EMP.PAY_HIST DISABLE CONSTRAINT SYS_C0082906; Table altered. Then drop the unique index. SQL> DROP INDEX EMP.PAY_HIST_PK; Index dropped. Data Loading Now, we can insert into the table. SQL> INSERT /*+ APPEND */ INTO PAY_HIST SELECT * FROM PAY_HIST_BAK; 3128041760 rows created. Create Primary Key Index We need to get the primary key back. First, create the original index. SQL> CREATE UNIQUE INDEX EMP.PAY_HIST_PK ON EMP.PAY_HIST (PAY_ID, PAY_CUST_ID, ISSUE_DATE) ONLINE; Index created. Of course, creating such unique index of a big table may take some times to complete, but it's worth it overall. The last step, we enable the primary key constraint. SQL> ALTER TABLE EMP.PAY_HIST ENABLE CONSTRAINT SYS_C0082906; Table altered.