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;

OCEJPA(1Z0-898) Preparation Tips

OCEJPA(1Z0-898) Preparation Tips from Guo Albert