9 Replies Latest reply: Apr 26, 2013 11:36 AM by sb92075 RSS

    IMP-00017: following statement failed with ORACLE error 942

    472969
      In my DB Import I am getting the following error, can anybody help me out what may be the reason behind it and appropriate solution.
      The Error is:

      IMP-00017: following statement failed with ORACLE error 942:
      "ALTER TABLE "PRMSELINCPUBT" ADD CONSTRAINT "PRMPRMCHPT_PRMSELINCPUBT_FK" FO"
      "REIGN KEY ("PRM_IDT","CHP_NUM") REFERENCES "PRMPRMCHPT" ("PRM_IDT","CHP_NUM"
      "")"

      Thanks in advance
      Kamal Shrivastava
        • 1. Re: IMP-00017: following statement failed with ORACLE error 942
          464759
          This error:

          $ oerr ora 942
          00942, 00000, "table or view does not exist"
          // *Cause:
          // *Action:

          generally means: that the table does not exist or that you don't have the right privileges to run the statement.

          Are you sure this table exist ?
          Do you have any other error before ?
          How has been the export command run (which user, which schema) ?
          How how the import command run (which user, which schema) ?
          • 2. Re: IMP-00017: following statement failed with ORACLE error 942
            464759
            One reason could be that the imported table is a child of another table that is not imported: there is a foreign key constraint and the parent table is not imported).
            Is it the case ?
            • 3. Re: IMP-00017: following statement failed with ORACLE error 942
              472969
              It doesn't have any foreign key constraints. There is enough space in tabasespace. Table does exists. Thats I wonder why this error is comming on, the full description of the error is
              IMP-00017: following statement failed with ORACLE error 942:
              "ALTER TABLE "PRMSELINCPUBT" ADD CONSTRAINT "PRMPRMCHPT_PRMSELINCPUBT_FK" FO"
              "REIGN KEY ("PRM_IDT","CHP_NUM") REFERENCES "PRMPRMCHPT" ("PRM_IDT","CHP_NUM"
              "")"
              IMP-00003: ORACLE error 942 encountered
              ORA-00942: table or view does not exist

              I wonder why it is saying table or view does not exist though the table is there.
              • 4. Re: IMP-00017: following statement failed with ORACLE error 942
                563614
                I am getting a similar problem.
                My export is from a 9i database.
                My import is into a 10g database.
                Tables are created without any problem but the index creations are failing with:
                IMP-00017: following statement failed with ORACLE error 942:
                • 5. Re: IMP-00017: following statement failed with ORACLE error 942
                  Maran Viswarayar
                  Full export? i dont think so
                  • 6. Re: IMP-00017: following statement failed with ORACLE error 942
                    Eduardo Legatti
                    Hi,

                    >>I wonder why it is saying table or view does not exist though the table is there.
                    Can you get run this statement manually without problems ?

                    Cheers
                    • 7. Re: IMP-00017: following statement failed with ORACLE error 942
                      654339
                      Hi I faced the same error in a similar scenario.
                      I tried executing these scripts as sysdba
                      But the same error appears.

                      SQL> @create_IDX_BMUSRBDL_STS_BILL_NXTBEGIN.sql
                      2 ;
                      CREATE INDEX "IDX_BMUSRBDL_STS_BILL_NXTBEGIN" ON "BM_USER_BUNDLE" (NVL("NEXT_PERIOD_BEGIN_DATE",TIMESTAMP' 5000-01-01 00:00:00.000000000') , "BILLABLE" , "STATUS" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING
                      *
                      ERROR at line 1:
                      ORA-00942: table or view does not exist



                      But later I tried executing these as the user which is being just imported.
                      I worked!!!
                      SQL> @create_IDX_BMUSRBDL_STS_BILL_NXTBEGIN.sql

                      Index created.

                      SQL>

                      I guess the import command tries to execute the commands for creating the indexes as sysdba and the sysdba do not have the specified table, But when u log in as the user who is having the table the command executes well.
                      • 8. Re: IMP-00017: following statement failed with ORACLE error 942
                        1005786
                        Kamal Shrivastava wrote:
                        In my DB Import I am getting the following error, can anybody help me out what may be the reason behind it and appropriate solution.
                        The Error is:

                        IMP-00017: following statement failed with ORACLE error 942:
                        "ALTER TABLE "PRMSELINCPUBT" ADD CONSTRAINT "PRMPRMCHPT_PRMSELINCPUBT_FK" FO"
                        "REIGN KEY ("PRM_IDT","CHP_NUM") REFERENCES "PRMPRMCHPT" ("PRM_IDT","CHP_NUM"
                        "")"

                        Thanks in advance
                        Kamal Shrivastava
                        Hi Kamal!

                        I recently encountered this kind of error, which I resolved today. Surprisingly, I have been using the same imp statement without errors until now.

                        In your imp statement set show=y then try again. You may need to recreate the tablespace and the user you are importing to. i.e. recreate "bulk" user and the tablespace it belongs to.

                        imp userid=admin/pass file=mydump.dmp log=mydump.log fromuser=admin touser=bulk show=y

                        Hearty Regards,
                        Adeleke
                        • 9. Re: IMP-00017: following statement failed with ORACLE error 942
                          sb92075
                          Adeleke,

                          Please wake up before responding again.
                          Kamal posted his problem more the SEVEN years ago.
                          I doubt he will care about your untimely response now.


                          How do I ask a question on the forums?
                          SQL and PL/SQL FAQ