-
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
Last edited by areeb; 06-22-2007 at 03:30 PM.
-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks PAVB i'll do that and let you know.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|