Monitor Table for duplicate inserts in Oracle 11g

Question

We have a table in Oracle 11g DB which has millions of records. For a few months, we have noticed that duplicate records are getting inserted into the table. Is there any way to alert via email when the duplicate records get inserted into the table? We are in the process of creating a unique index for the table, but it will take sometime. Meanwhile, can an email-alert be created to notify us when duplicate records are getting inserted?

We have OEM installed to monitor this Oracle DB.

Answer 1

If selecting from table takes a long time, I guess you don't want to intercept duplicates as they happen because every insert/update would kill performance even more than it does now.

Therefore, if table isn't in use 24/7, perhaps you could schedule that check and either notify someone or - even better - take action immediately.

That would be a stored procedure, e.g.

create or replace procedure p_del_dup as
begin
  delete from your_table a
    where a.rowid > (select min(b.rowid) 
                     from your_table b
                     where b.future_unique_column = a.future_unique_column
                    );
end;

---

Live example, based on Scott's sample schema. I'll create a table that contains EMP data, enter some duplicates and delete them.

SQL> create table test as select * from emp where deptno in (10, 20);

Table created.

SQL> create or replace procedure p_del_dup as
  2  begin
  3    delete from test a
  4      where a.rowid > (select min(b.rowid)
  5                       from test b
  6                       where b.empno = a.empno   --> column(s) which will enforce uniqueness
  7                      );
  8  end;
  9  /

Procedure created.

Create a database job; schedule it to run at 02:00 every night

SQL> declare
  2    x number;
  3  begin
  4    dbms_job.submit
  5      ( job       => x
  6       ,what      => 'p_del_dup;'
  7       ,next_date => to_date('07.04.2020 02:00:00','dd/mm/yyyy hh24:mi:ss')
  8       ,interval  => 'TRUNC (SYSDATE+1) + 2 / 24'
  9       ,no_parse  => false
 10      );
 11    dbms_output.put_line('Job Number is: ' || to_char(x));
 12    commit;
 13  end;
 14  /
Job Number is: 104

PL/SQL procedure successfully completed.

OK, let's now insert some duplicates (all from deptno = 10):

SQL> insert into test select * from emp where deptno = 10;

3 rows created.

SQL> select * from test order by deptno, ename;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7839 KING       PRESIDENT            17.11.81       5000                    10
      7839 KING       PRESIDENT            17.11.81       5000                    10
      7934 MILLER     CLERK           7782 23.01.82       1300                    10
      7934 MILLER     CLERK           7782 23.01.82       1300                    10
      7876 ADAMS      CLERK           7788 12.01.83       1100                    20
      7902 FORD       ANALYST         7566 03.12.81       3000                    20
      7566 JONES      MANAGER         7839 02.04.81       2975                    20
      7788 SCOTT      ANALYST         7566 09.12.82       3000                    20
      7369 SMITH      CLERK           7902 17.12.80        800                    20

11 rows selected.

I won't wait until 02:00 so I'll run the job manually. Remember, its ID is 104?

SQL> exec dbms_job.run(104);

PL/SQL procedure successfully completed.

SQL> select * from test order by deptno, ename;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7839 KING       PRESIDENT            17.11.81       5000                    10
      7934 MILLER     CLERK           7782 23.01.82       1300                    10
      7876 ADAMS      CLERK           7788 12.01.83       1100                    20
      7902 FORD       ANALYST         7566 03.12.81       3000                    20
      7566 JONES      MANAGER         7839 02.04.81       2975                    20
      7788 SCOTT      ANALYST         7566 09.12.82       3000                    20
      7369 SMITH      CLERK           7902 17.12.80        800                    20

8 rows selected.

SQL>

OK, duplicates are silently deleted.

If you want, you can modify the procedure and - using UTL_MAIL package - send an e-mail message to someone. If number of rows deleted is enough, you'd send SQL%ROWCOUNT number. Or, send whatever you want.