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.
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.