Error message looks like this
Error message => ORA-00001: unique constraint (schema.unique_constraint_name) violated
ORA-00001 occurs when: "a query tries to insert a "duplicate" row in a table". It makes an unique constraint to fail, consequently query fails and row is NOT added to the table."
Solution:
Find all columns used in unique_constraint, for instance column a, column b, column c, column d collectively creates unique_constraint and then find the record from source data which is duplicate, using following queries:
-- to find <<owner of the table>> and <<name of the table>> for unique_constraint
select *
from DBA_CONSTRAINTS
where CONSTRAINT_NAME = '<unique_constraint_name>';
Then use Justin Cave's query (pasted below) to find all columns used in unique_constraint:
SELECT column_name, position
FROM all_cons_columns
WHERE constraint_name = <<name of constraint from the error message>>
AND owner = <<owner of the table>>
AND table_name = <<name of the table>>
-- to find duplicates
select column a, column b, column c, column d
from table
group by column a, column b, column c, column d
having count (<any one column used in constraint > ) > 1;
you can either delete that duplicate record from your source data (which was a select query in my particular case, as I experienced it with "Insert into select") or modify to make it unique or change the constraint.