SOLVED

How to Resolve ORA-01502 Error

Asked by OracleDba••23 views•oracle
1
2
3
4
5
6
7
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
#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
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.

Hope it Helps!
OracleDba•

Post Your Solution