Skip to Main Content
  • Questions
  • Buffer parameter in the legacy IMP utility and unique constraints

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Curious_DBA.

Asked: May 13, 2021 - 8:48 pm UTC

Last updated: May 19, 2021 - 3:44 am UTC

Version: Oracle enterprise database 10g and forward

Viewed 100+ times

You Asked

Hi,
When using the legacy IMP tool, if you have specified ignore=y commit=y and buffer=9999999 what is the behavior of IMP when a unique constraint is violated by a row in the buffer?
Assuming the buffer holds more than one row, will IMP reject the entire buffer or only reject the specific offending row/rows but then insert the other rows that are in the buffer.
I am unable to find documentation for the behavior in this specific case.

Thanks in advance for any info.

and we said...

Just the record in error will be rejected.

eg

C:\Users\hamcdc>exp userid=scott/tiger@db19_pdb1 tables=emp file=c:\tmp\emp.dmp

Export: Release 19.0.0.0.0 - Production on Fri May 14 14:31:00 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported


Then I did this in my other schema


SQL> create table emp as select * from scott.emp
  2  where rownum = 1;

Table created.

SQL> alter table emp add primary key ( empno );

Table altered.


and then import did this

C:\Users\hamcdc>imp userid=mcdonac@db19_pdb1 ignore=y commit=y buffer=9999999 fromuser=scott touser=mcdonac file=c:\tmp\emp.dmp

Import: Release 19.0.0.0.0 - Production on Fri May 14 14:32:35 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

Export file created by EXPORT:V19.00.00 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
IMP-00403:

. importing SCOTT's objects into MCDONAC
. . importing table                          "EMP"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (MCDONAC.SYS_C0047832) violated
Column 1 7369
Column 2 SMITH
Column 3 CLERK
Column 4 7902
Column 5 17-DEC-1980:00:00:00
Column 6 800
Column 7
Column 8 20         13 rows imported
About to enable constraints...
Import terminated successfully with warnings.


Rating

  (1 rating)

Comments

A reader, May 14, 2021 - 1:32 pm UTC

Thank you for taking the time to prove this out with a test case
Connor McDonald
May 19, 2021 - 3:44 am UTC

glad we could help

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.