import error-ORA-00001: unique constraint (GLISADM.C_CUS_SK_01) violated

Hi All,
I am doing an import on HP-UX(11.2) database is 10.2.0.2 ,database is empty before import just precreated tablespaces,target characterset is AL32UTF8
and source is 9.2.0.5.0 on hpux characterset is UTF8,but i am getting the following error when there is no data in the tables(clean) prior to import.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options

Export file created by EXPORT:V09.02.00 via conventional path

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

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses UTF8 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. importing GLISADM's objects into GLISADM
. . importing table "PLAN_TABLE" 0 rows imported
. . importing table "T_ANSWER" 0 rows imported
. . importing table "T_APL" 29900 rows imported
. . importing table "T_CALL" 81761 rows imported
. . importing table "T_CDPART" 1379 rows imported
. . importing table "T_CODEWORD" 2235054 rows imported
. . importing table "T_COUNTRY" 259 rows imported
. . importing table "T_CUSTOMER"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (GLISADM.C_CUS_SK_01) violated
Column 1 139351
Column 2 ���ũҵ��ɽ��ʡ��
Column 3 ����
Column 4 570
Column 5 V
Column 6 CUSTST
Column 7 ɽ��ʡ���
Column 8
Column 9
Column 10 ���γ��·168����Ӵ���Ƽ�...
Column 11
Column 12
Column 13
Column 14
Column 15
Column 16
Column 17 12-JUL-2004:07:46:31
Column 18 SAP5000
Column 19 20-APR-2005:23:25:55


Here is my parfile for export
userid=exp_full/exp
buffer=500000000
consistent=y
compress=n
full=y
log=expGLIS1.log
statistics=none
~
--------------------------
import parfile
userid='/ as sysdba'
buffer=500000000
ignore=y
full=y
rows=y
indexes=n
constraints=n
grants=y
log=impGLIS11.log
statistics=none
commit=y
~

any suggestion would really helpful

That has nothing to deal with the imp. U r just trying to imprt a row, which already exists in the table. Does this table exists? I guess it does

yeah just table with no data in it ,as i drop the schema and precreated that table with no data.

That means you have duplicate rows on your dump file

source have constraints enabled and has no duplicate data,dont know how i am getting duplicates.

If I remember right you said you have pre-created the offending table.
Chances are you did it wrong or, you ran your import twice.

i drop the schema couple of times recreated it with empty table but still no
luck.

Here is the tthe table create statement original so as to avoid ORA-12899: value too large for column "GLISADM"."T_CUSTOMER"."CUS_COMPANY" (actual: 81, maximum: 80)

CREATE TABLE "GLISADM"."T_CUSTOMER"
( "CUS_CUSTOMER_ID" NUMBER(10,0),
"CUS_COMPANY" VARCHAR2(150),
"CUS_CONTACT_NAME" VARCHAR2(150),
"CUS_COUNTRY_CODE" VARCHAR2(4),
"CUS_STATUS" CHAR(1) DEFAULT 'V',
"CUS_TYPE" VARCHAR2(6),
"CUS_ADDR1" VARCHAR2(200),
"CUS_ADDR2" VARCHAR2(200),
"CUS_ADDR3" VARCHAR2(200),
"CUS_ADDR4" VARCHAR2(200),
"CUS_PHONE" VARCHAR2(20),
"CUS_FAX" VARCHAR2(30),
"CUS_EMAIL" VARCHAR2(60),
"CUS_COMPANY_DOMAIN" VARCHAR2(60),
"CUS_HP_CODE" VARCHAR2(10),
"CUS_COMMENTS" VARCHAR2(200),
"CUS_CREATE_DATE" DATE,
"CUS_CREATE_BY" VARCHAR2(10),
"CUS_LASTMODIF_DATE" DATE,
"CUS_LASTMODIF_BY" VARCHAR2(10),
CONSTRAINT "C_CUS_SK_01" UNIQUE ("CUS_COMPANY", "CUS_CONTACT_NAME","CUS_COUNTRY_CODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "GLIS_INDEX" ENABLE,
CONSTRAINT "C_CUS_PK" PRIMARY KEY ("CUS_CUSTOMER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "GLIS_INDEX" ENABLE,
CONSTRAINT "C_CUS_FK_01" FOREIGN KEY ("CUS_TYPE")
REFERENCES "GLISADM"."T_PARAMETER" ("PAR_TYPE_KEY") ENABLE,
CONSTRAINT "C_CUS_FK_02" FOREIGN KEY ("CUS_COUNTRY_CODE")
REFERENCES "GLISADM"."T_COUNTRY" ("COU_COUNTRY_CODE") ENABLE,
CONSTRAINT "C_CUS_FK_CB" FOREIGN KEY ("CUS_CREATE_BY")
REFERENCES "GLISADM"."T_USER" ("USE_EMPLOYEE_NUM") ENABLE,
CONSTRAINT "C_CUS_FK_MB" FOREIGN KEY ("CUS_LASTMODIF_BY")
REFERENCES "GLISADM"."T_USER" ("USE_EMPLOYEE_NUM") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 134217728 NEXT 134217728 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "GLIS_DATA"

as it contains some foreign keys i have to remove ,so here is the modified version,iam using this to create table

CREATE TABLE "GLISADM"."T_CUSTOMER"
( "CUS_CUSTOMER_ID" NUMBER(10,0),
"CUS_COMPANY" VARCHAR2(150),
"CUS_CONTACT_NAME" VARCHAR2(150),
"CUS_COUNTRY_CODE" VARCHAR2(4),
"CUS_STATUS" CHAR(1) DEFAULT 'V',
"CUS_TYPE" VARCHAR2(6),
"CUS_ADDR1" VARCHAR2(200),
"CUS_ADDR2" VARCHAR2(200),
"CUS_ADDR3" VARCHAR2(200),
"CUS_ADDR4" VARCHAR2(200),
"CUS_PHONE" VARCHAR2(20),
"CUS_FAX" VARCHAR2(30),
"CUS_EMAIL" VARCHAR2(60),
"CUS_COMPANY_DOMAIN" VARCHAR2(60),
"CUS_HP_CODE" VARCHAR2(10),
"CUS_COMMENTS" VARCHAR2(200),
"CUS_CREATE_DATE" DATE,
"CUS_CREATE_BY" VARCHAR2(10),
"CUS_LASTMODIF_DATE" DATE,
"CUS_LASTMODIF_BY" VARCHAR2(10),
CONSTRAINT "C_CUS_SK_01" UNIQUE ("CUS_COMPANY", "CUS_CONTACT_NAME","CUS_COUNTRY_CODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "GLIS_INDEX" ENABLE,
CONSTRAINT "C_CUS_PK" PRIMARY KEY ("CUS_CUSTOMER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "GLIS_INDEX" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 134217728 NEXT 134217728 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "GLIS_DATA"
i am dropping the schema and creating it back with this table(nodata)obiviously.

1- Pre create your table with no PK
2- Load it
3- Find out which ones are the duplicate rows
4- Fix it
5- Build your PK

Thanks PAVB i'll do that and let you know.

Yeah did as you said PAVB here is the error iam get.

SQL> delete from GLISADM.T_CUSTOMER where rowid in (
2 SELECT rowid FROM GLISADM.T_CUSTOMER a
3 WHERE a.rowid > (SELECT MIN(b.rowid)
4 FROM GLISADM.T_CUSTOMER b
WHERE b.CUS_COMPANY = a.CUS_COMPANY
and b.CUS_CONTACT_NAME = a.CUS_CONTACT_NAME
5 6 7 and b.CUS_COUNTRY_CODE = b.CUS_COUNTRY_CODE));
delete from GLISADM.T_CUSTOMER where rowid in (
*
ERROR at line 1:
ORA-23474: definition of "GLISADM"."T_CUSTOMER" has changed since generation of
replication support
ORA-26500: error on caching "GLISADM"."T_CUSTOMER"

any suggestions

I'm starting to feel you are not telling us all what's going around.

Is somehow replication involved in your scenario?

I am supposed to do migration for this database from 9i to 10g and the
application team did't specify anything about replication or anything
just gave access to source and expected me to do it,so anyway

1)i exported full but started getting errors ORA-12899 while importing for table T_CUSTOMER so imported everything then i truncated the table T_CUSTOMER and increased the width of the column to 150(orignal 80)
but it was giving error ORA-00001 while loading this table so as suggested
pre-created table with no PK and loaded data then i thought of deleting
any duplicate data but it gave error ORA-23474 then i dropped the idea of deleting the duplicate rows and started enabling the constraints and got the
following errors

IMP-00017: following statement failed with ORACLE error 23313:
"BEGIN SYS.DBMS_REPCAT.REPCAT_IMPORT_REPSCHEMA; END;"
IMP-00003: ORACLE error 23313 encountered
ORA-23313: object group "PUBLIC"."EXTERNAL" is not mastered at GLISD
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_REPCAT", line 1065
ORA-06512: at line 1
. importing GLISADM's objects into GLISADM
. importing SYSTEM's objects into SYSTEM
. importing GLISADM's objects into GLISADM
. importing REPDBA's objects into REPDBA
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
About to enable constraints...
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "REPCAT$_GENERATED" ENABLE CONSTRAINT "REPCAT$_REPGEN_PRNT""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (SYSTEM.REPCAT$_REPGEN_PRNT) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "REPCAT$_GENERATED" ENABLE CONSTRAINT "REPCAT$_REPGEN_PRNT2""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (SYSTEM.REPCAT$_REPGEN_PRNT2) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "REPCAT$_REPCOLUMN" ENABLE CONSTRAINT "REPCAT$_REPCOLUMN_FK""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (SYSTEM.REPCAT$_REPCOLUMN_FK) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "REPCAT$_REPPROP" ENABLE CONSTRAINT "REPCAT$_REPPROP_PRNT""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (SYSTEM.REPCAT$_REPPROP_PRNT) - parent keys not found
. importing GLISADM's objects into GLISADM
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_APL" ENABLE CONSTRAINT "C_APL_FK_01""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (GLISADM.C_APL_FK_01) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_APL" ENABLE CONSTRAINT "C_APL_FK_02""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (GLISADM.C_APL_FK_02) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_APL" ENABLE CONSTRAINT "C_APL_FK_02""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (GLISADM.C_APL_FK_02) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_APL" ENABLE CONSTRAINT "C_APL_FK_CB""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (GLISADM.C_APL_FK_CB) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_CALL" ENABLE CONSTRAINT "C_CAL_FK_01""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (GLISADM.C_CAL_FK_01) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_CALL" ENABLE CONSTRAINT "C_CAL_FK_02""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (GLISADM.C_CAL_FK_02) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_CALL" ENABLE CONSTRAINT "C_CAL_FK_04""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (GLISADM.C_CAL_FK_04) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_CALL" ENABLE CONSTRAINT "C_CAL_FK_05""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (GLISADM.C_CAL_FK_05) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_CALL" ENABLE CONSTRAINT "C_CAL_FK_06""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (GLISADM.C_CAL_FK_06) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_CALL" ENABLE CONSTRAINT "C_CAL_FK_07""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (GLISADM.C_CAL_FK_07) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_CALL" ENABLE CONSTRAINT "C_CAL_FK_CB""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (GLISADM.C_CAL_FK_CB) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_CALL" ENABLE CONSTRAINT "C_CAL_FK_MB""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (GLISADM.C_CAL_FK_MB) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "T_CDPART" ENABLE CONSTRAINT "C_CDP_FK_CB""

hope i made things clearer this time .

Thanks

Have you read the error message? "parent keys not found" ... what it tells you?

That there are some records in child table that does not exists in the parent,
so either got to add those records to parent table or drop orphan records
from child right?what is the query for obtaining what parent table
is the foreign key referencing to and to obtain the orphan records ,i'll
apperciate that.

Thanks PAVB for your help

Thanks

What would be impact of this command on the consistency of data,
alter table xyz modify constraint abc enable novalidate

Quote:

---

Originally Posted by areeb

alter table xyz modify constraint abc enable novalidate

---

It will create the constraint letting whatever data -good or bad in terms or referential integrity- you have there and, just start validating referencial integrity from that point in time into the future.

Thanks PAVB for execellent explaination.I really apperciate your input on this, really grateful of you.

Thanks