DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

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

  1. #1
    Join Date
    Jul 2005
    Posts
    87

    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.

  2. #2
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  3. #3
    Join Date
    Jul 2005
    Posts
    87
    yeah just table with no data in it ,as i drop the schema and precreated that table with no data.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  5. #5
    Join Date
    Jul 2005
    Posts
    87
    source have constraints enabled and has no duplicate data,dont know how i am getting duplicates.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  7. #7
    Join Date
    Jul 2005
    Posts
    87
    i drop the schema couple of times recreated it with empty table but still no
    luck.

  8. #8
    Join Date
    Jul 2005
    Posts
    87
    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.

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  10. #10
    Join Date
    Jul 2005
    Posts
    87
    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
  •  


Click Here to Expand Forum to Full Width