Imp-00003 Oracle Error 1 Encountered Ora-00001 Unique Constraint
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta imp-00019 row rejected due to oracle error 1 Discuss the workings and policies of this site About Us Learn ora-00001 unique constraint violated more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up IMP-00019: row rejected due to ORACLE error 1 up vote 0 down vote favorite I'm working on import data to oracle. I create new user thelinh and new database blogi in oracle. Currently there is no data in database blogi. Then run below command: IMP thelinh/Thelinh05@blogi FILE= D:\source\rabbit\db_dmp\billuser.dmp FULL=Y IGNORE=Y log=log.txt The log show many error like below: IMP-00019: row rejected due to ORACLE error 1 IMP-00003: ORACLE error 1 encountered ORA-00001: unique constraint (SYSTEM.REPCAT$_AUDIT_ATTRIBUTE_PK) violated. Please help me to fix this error. oracle import constraints share|improve this question asked Jan 18 at 3:38 Thelinh Truong 1261113 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote It means that you are trying to import the row that already exists in the database. Are you sure you need to import SYSTEM schema? I think it is better to import only schemas that you need and drop/clear them before the import. Did this import complete? If so I believe you can ignore this error. You can also use the ignore parameter. Also, if you can use the datapump (expdp/impdp) to export/imprt this has TABLE_EXISTS_ACTION, that lets you specify the action to take if imported object already exists (APPEND, REPLACE, SKIP and TRUNCATE). share|improve this answer answered Jan 18 at 4:32 stee1rat
Last updated: April 19, 2010 - 8:17 am UTC Category: Database � Version: 10.2.0.4 Latest Followup You Asked I've been instructed to take some ddl from one database, and data from another, for just one user, but it isn't a perfect match and on the import of the data I was getting slews and slews of IMP-00019: row rejected due to ORACLE error 1 IMP-00003: ORACLE error 1 encountered ORA-00001: unique constraint (DAS.PKAST) violated so I killed the import, rebuilt, and disabled all the constraints with select 'alter table http://stackoverflow.com/questions/34846927/imp-00019-row-rejected-due-to-oracle-error-1 '||a.table_name||' disable constraint '|| a.constraint_name||'cascade ;' from user_constraints a / and received no errors when disabling them. I know there is a problem ahead, but want to get the data in and then report back what constraints are not going to work. But this confuses me. The constraints are disabled. Then.. I got the exact same error.. on a disabled constraint. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2560551900346621763 So I go straight to the table and try inserting a duplicate row, and sure enough, a unique constraint violation, but the constraint is disabled. Can you explain this output? SQL> desc ast_pty; Name Null? Type ----------------------------------------- -------- ---------------------------- APPL_ID NUMBER(38) USER_ID NUMBER(38) ROLE_ID NUMBER(38) UPDATED_BY VARCHAR2(30) UPDATE_TIMESTAMP DATE SQL> insert into ast_pty values (1,1,1,'a',sysdate); 1 row created. SQL> / insert into ast_pty values (1,1,1,'a',sysdate) * ERROR at line 1: ORA-00001: unique constraint (DAS.PKAST) violated SQL> select status from user_constraints 2 where constraint_name = 'PKAST'; STATUS -------- DISABLED SQL> ?? and we said... sounds like a unique index. It'll raise the same ora-00001. ops$tkyte%ORA10GR2> create table t ( x int ); Table created. ops$tkyte%ORA10GR2> create UNIQUE index t_unique on t(x); Index created. ops$tkyte%ORA10GR2> alter table t add constraint t_unique unique(x); Table altered. ops$tkyte%ORA10GR2> alter table t disable constraint t_unique; Table altered. ops$tkyte%ORA10GR2> insert into t values ( 1 ); 1 row created. ops$tkyte%ORA10GR2> insert into t values ( 1 ); insert into t values ( 1 ) * ERROR at line 1: ORA-00001: unique constraint (OPS$TKYTE.T_UNIQUE) violated ops$tkyte%ORA10GR2> drop in
20 May 2005 08:47 hiral Messages: 3Registered: May 2005 Junior Member hi we are having two schemas sc1 and sc2 at two different places. sc1 is having all tables with data and constraints while sc2 is having only structure http://www.orafaq.com/forum/t/46250/ of same tables with constraints but no data. in sc1 there r some tables having self refferential integrity constraints ,i.e. P rimary and Foreign key constraints. we are trying to tranfer data from Sc1 to Sc2 & for that we are using export and import utility of oracle. when we import data from sc1.DMP to sc2, we phase problem while importing tables which have 'self refferential integrity constraints' b'coz import oracle error utility tries to import child row before parent row and finally is rejected on parent not found. kindly give solution how to import all data to sc2. thanking u Hiral patel Bhadresh patel [DBA-L&T]. Report message to a moderator Re: Problem while importing [message #120577 is a reply to message #120565] Fri, 20 May 2005 09:19 Mahesh Rajendran Messages: 10672Registered: March 2002 Location: oracleDocoVille Senior MemberAccount Moderator the oracle error 1 import should handle it. Please see the session. Else, you can disable all the constraints before loading load ( import with constraints=n) enable the consraints. -- -- set up the tables -- scott@9i > alter table emp add constraint pk_eno primary key (empno); Table altered. scott@9i > alter table emp add constraint self_1 foreign key (mgr) references emp(empno); Table altered. -- -- Test the constraints -- scott@9i > insert into emp values (111,'sam','newbie',9999,sysdate,100,10,10); insert into emp values (111,'sam','newbie',9999,sysdate,100,10,10) * ERROR at line 1: ORA-02291: integrity constraint (SCOTT.SELF_1) violated - parent key not found scott@9i > insert into emp values (111,'sam','newbie',7698,sysdate,100,10,10); 1 row created. scott@9i > commit; Commit complete. -- -- export the table. -- scott@9i > !exp scott/tiger tables=(emp) indexes=y constraints=y statistics=none Export: Release 9.2.0.4.0 - Production on Fri May 20 09:01:03 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.4.0 - Production Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table EMP 15 rows exported Export terminated successfully without warnings. -- -- import the table to another