This discussion is archived
9 Replies Latest reply: Apr 26, 2013 9:36 AM by sb92075 RSS

IMP-00017: following statement failed with ORACLE error 942

472969 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    Full export? i dont think so
  • 6. Re: IMP-00017: following statement failed with ORACLE error 942
    Eduardo Legatti Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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