Total Pageviews

2013/07/24

ORA-01452: cannot CREATE UNIQUE INDEX;

Problem
As DBA rebuilt index, it produced error report as following:

Root Cause
Based on the NIGT021 schema, it only has one primary key. Therefore, it may has abnormal and duplicate data in NIGT021.

Solution
1. Figure out abnormal data
2. Delete abnormal data
3. rebuild index

Here is the SQL statement template to find duplicate value in this table
   select column_name, count(column_name)
   from [table]
   group by column_name
   having count (column_name) > 1;
Here is an example to apply to NIGT021
   select pnsh_tp, count(pnsh_tp)
   from nigt021
   group by pnsh_tp
   having count (pnsh_tp) > 1;

No comments: