ORA-00001: unique constraint violated

IGNORE_ROW_ON_DUPKEY_INDEX hint is on rescue. This is the NEW feature in 11gR2 applied on INSERT statements, wherein duplicate values will be ignored rather causing ORA-00001 error.

Scenario: Development team wanted one of the STAGING table to be refreshed from PROD table without replacing the existing rows in the staging table.

Solution:

For understanding purpose I have created table EMP_BK as STAGING table and EMP as PROD table.

Existing table data:

Existing_table-data

SQL> insert into EMP_BK (empno,ename,job,mgr,hiredate,sal,comm,deptno) 
select empno,ename,job,mgr,hiredate,sal,comm,deptno from EMP;

insert into EMP_BK (empno,ename,job,mgr,hiredate,sal,comm,deptno) 
select empno,ename,job,mgr,hiredate,sal,comm,deptno from EMP;
*
error at line 1:
ora-00001: unique constraint (scott.pk_emp_bk) violated

SQL> insert /*+ ignore_row_on_dupkey_index(emp_bk,pk_emp_bk) */  
into EMP_BK (empno,ename,job,mgr,hiredate,sal,comm,deptno) 
select empno,ename,job,mgr,hiredate,sal,comm,deptno from EMP;

1 row created.

SQL> commit;

Commit complete.

Verify by querying the EMP_BK table


SQL> SELECT * FROM EMP_BK;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH      CLERK           7902 17-DEC-80        800                    20
7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
7566 JONES      MANAGER         7839 02-APR-81       2975                    20
7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
7839 KING       PRESIDENT            17-NOV-81       5000                    10
7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
7900 JAMES      CLERK           7698 03-DEC-81        950                    30
7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
1022 RAHEEL     DBA             7782 29-DEC-13       2000          0         30

15 rows selected.

Cheers,
Raheel

About these ads

About Raheel Syed

Oracle DBA
This entry was posted in 11gR2, New Features and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s