ORA-942

When I had my first Oracle DBA course some years before the start of this millennium I made a special mental note about a very common error message, ORA-00942, “table or view does not exist”. In most programming languages there are a similar message for a common situation; when you refer to an object that does not exist, quite often because you wrote the name wrong. What I found a bit remarkable when I heard about it first time was that ORA-942 may mean two things, either that the table or view does indeed not exist, or you do not have access to it. An example of the latter is when some user has created a table in her schema, and you try to query it, but you have not been granted a privilege to do so. Instead of you receiving some error message like “access denied”, Oracle responds with the same error message; “table or view does not exist”, even if it does exist. It may have added “for you” to make it more correct. I think there is a good reason why this is so, and I think it has to do with optimisation. ...

Say you define a DP job from the API (DBMS_DATAPUMP) you may end up with jobs with status NOT RUNNING until you get it right. Verify if you have such a job with select job_name,state from user_datapump_jobs; Then you may try to remove it with: declare l_h number; begin l_h:=dbms_datapump.attach('YOUR_JOB'); dbms_datapump.stop_job(l_h,immediate=>1); commit; end; / If the query above still reports the job with the same status it can be removed by dropping the master table, according to Note 336014.1: ...