2 Replies Latest reply: Sep 24, 2013 3:43 PM by user11318851 RSS

    datapump import error

    Rabi786
      Hi

      I am using oracle 10g R2 10.2.0.3 . While importing an schema i got the following error


      ORA-39083: Object type TABLE failed to create with error:
      ORA-00907: missing right parenthesis
      Failing sql is:
      CREATE TABLE "CARDPRO"."CP_VSIMMX" ("VX_SEQ_NO" NUMBER(6,0) NOT NULL ENABLE, "VX_TRXN_CD" NUMBER(2,0), "VX_SRC_BIN" NUMBER(6,0), "VX_DEST_
      BIN" NUMBER(6,0), "VX_PROC_YDDD" NUMBER(4,0), "VX_TRXN_DATE" NUMBER(4,0), "VX_CARDHOLDER_NO" CHAR(19), "VX_PRODUCT_CD" NUMBER(4,0), "VX_EX
      PIRY_DATE" NUMBER(4,0), "VX_ACQUIRER_REFNO" CHAR(23), "VX_AUTHORZ_CD" CHAR(6), "VX_USAGE_CD" CHAR(1), "VX_TRXN_CURR_AMT" NU
      ORA-39083: Object type TABLE failed to create with error:
      ORA-00907: missing right parenthesis
      Failing sql is:
      CREATE TABLE "CARDPRO"."CP_VSLCTX" ("VL_SEQ_NO" NUMBER(6,0) NOT NULL ENABLE, "VL_TRXN_CD" NUMBER(2,0), "VL_SRC_BIN" NUMBER(6,0), "VL_DEST_
      BIN" NUMBER(6,0), "VL_PROC_YDDD" NUMBER(3,0), "VL_TRXN_DATE" NUMBER(4,0), "VL_CARDHDR_NO" CHAR(19), "VL_PRODUCT_CD" NUMBER(4,0), "VL_EXPIR
      Y_DATE" NUMBER(4,0), "VL_ACQUIRER_REFNO" CHAR(23), "VL_AUTHORZ_CD" CHAR(6), "VL_USAGE_CD" CHAR(1), "VL_TRXN_CURR_AMT" NUMBE



      Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
      ORA-39112: Dependent object type OBJECT_GRANT:"CARDPRO" skipped, base object type TABLE:"CARDPRO"."CP_VSIMMX" creation failed
      ORA-39112: Dependent object type OBJECT_GRANT:"CARDPRO" skipped, base object type TABLE:"CARDPRO"."CP_VSLCTX" creation failed
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

      ORA-39112: Dependent object type INDEX:"CARDPRO"."PKCP_VSIMMX" skipped, base object type TABLE:"CARDPRO"."CP_VSIMMX" creation failed
      ORA-39112: Dependent object type INDEX:"CARDPRO"."CP_VSIMMX" skipped, base object type TABLE:"CARDPRO"."CP_VSIMMX" creation failed
      ORA-39112: Dependent object type INDEX:"CARDPRO"."PKCP_VSLCTX" skipped, base object type TABLE:"CARDPRO"."CP_VSLCTX" creation failed
      ORA-39112: Dependent object type INDEX:"CARDPRO"."CP_VSLCTX" skipped, base object type TABLE:"CARDPRO"."CP_VSLCTX" creation failed
      Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
      ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"CARDPRO"."PKCP_VSIMMX" creation failed
      ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"CARDPRO"."CP_VSIMMX" creation failed
      ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"CARDPRO"."PKCP_VSLCTX" creation failed
      ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"CARDPRO"."CP_VSLCTX" creation failed
      Processing object type SCHEMA_EXPORT/TABLE/COMMENT
      Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
      Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
      Processing object type SCHEMA_EXPORT/VIEW/VIEW
      Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
      ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"CARDPRO"."CP_VSIMMX" creation failed
      ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"CARDPRO"."CP_VSLCTX" creation failed


      I have tried myself to find out the reason but didn't find anything. Can you please share me your idea to find out the reason


      Regards

      Jav

      Edited by: user623166 on Jan 2, 2012 10:21 PM
        • 1. Re: datapump import error
          Srini Chavali-Oracle
          Pl post OS details, along with the complete expdp and impdp commands used. Are the charactersets of the source and target databases identical ?

          Data Pump EXCLUDE Parameter Errors          [Document 1339045.1]
          Impdp Fails With ORA-39083 ORA-907 ORA-39125 LPX-00230 When Importing Views          [Document 803043.1]

          HTH
          Srini
          • 2. Re: datapump import error
            user11318851

            I recently had a similar problem

             

            Do another import adding the parameters TABLES=<the failing table> and SQLFILE=<a convenient filename>.

             

            Look in the resulting file for something like this

             

            CREATE TABLE "<schema_name>"."<table_name>"

               ( "SYSTEM_ID" NUMBER NOT NULL ENABLE,

            <intermediate columns not show for space reasons>

            "<field_name>" VARCHAR2(1 BYTE) DEFAULT 'N'  -- RDT&E Standalone : Y-Yes;N-No,

            "<field_name>" VARCHAR2(100 BYTE),

            "CLASS" VARCHAR2(2 BYTE) DEFAULT '01'

               ) SEGMENT CREATION IMMEDIATE

              PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

            NOCOMPRESS LOGGING

                TABLESPACE "DATA" ;


            The code comment is causing three problems.  It displaces the comma, it has the & macro substitution character, and it has a semi-colon.  All three of these can cause the "missing right parenthesis" problem.

            I edited my sqlfile, pre-created the table, and redid the import for just that table with the TABLE_EXISTS_ACTION=append.