DBA Hub

📋Steps in this guide1/1

How to Resolve ORA-01502 Error

First of all, we need to disable the primary key constraint.

oracle configurationintermediate
by OracleDba
21 views
1

Overview

- Rebuild Index :

Code/Command (click line numbers to comment):

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
SQL> ALTER INDEX EMP_SALARY;

Index altered.

Or rebuild it online:

SQL> ALTER INDEX EMP_SALARY ONLINE;

Index altered.

SQL> ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE;

Session altered.

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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!