Query data that does not fulfill constraints in table in Oracle DB
Reporting Constraint Exceptions


  You must create an appropriate exceptions report table to accept
  information from the EXCEPTIONS option of the ENABLE clause before
  enabling the constraint. You can create an exception table by
  executing the UTLEXCPT.SQL script or the UTLEXPT1.SQL script.
  
  The following statement attempts to validate the PRIMARY KEY of the
  dept table, and if exceptions exist, information is inserted into a
  table named EXCEPTIONS:

ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;

  
  If duplicate primary key values exist in the dept table and the name
  of the PRIMARY KEY constraint on dept is sys_c00610, then the
  following query will display those exceptions:

SELECT * FROM EXCEPTIONS;

  
  The following exceptions are shown:

fROWID               OWNER      TABLE_NAME      CONSTRAINT
------------------  ---------  --------------  -----------
AAAAZ9AABAAABvqAAB  SCOTT      DEPT            SYS_C00610 
AAAAZ9AABAAABvqAAG  SCOTT      DEPT            SYS_C00610