ORA-00942

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.

If you enable sql trace, you can see Oracle runs queries against obj$ and objauth$.

select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 
from obj$ 
where owner#=:1 and name=:2 and namespace=:3 
and remoteowner is null 
and linkname is null and subname is null

select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from objauth$ 
where obj#=:1 
group by grantee#,privilege#,nvl(col#,0) 
order by grantee#

The first checks for the object's existence, the second looks for privileges granted on the object. By the way, the results from these queries may be cached so you won't always see these in the trace file. Exactly what is going on behind the scene here is unknown to me, and I also think that the implementation of this has changed during the years with different versions of the database. But if you look at the second query, that fact that no row is returned may come from either the fact that no privileges have been given, or that the table does not exist. So, instead of going an extra round in the dictionary to see if the table exists since no privileges exist on it, Oracle simply returns the same error message "table or view does not exist".

It is probably a good security practise to not inform unprivileged users about the existence of objects they have no access to, but I thought it was a smart optimisation by Oracle back then, because I was used to see error messages like "access denied". The way they have done this saves extra work. By know I think I have lost the attention of everyone except my best oracle nerd friends, so I thought I could sneak in a confession here at the end. Some years ago I started to practise ORA-942 in my own life. You know in those situations where people suspect you know some secret and start asking away about stuff you are not allowed to pass on? Instead of me having the burden of creating excuses or explaining why I can't tell, I simply answer "I don't know". That saves me a lot of extra work and I don't consider it a worse practice than mining other people's brain.

Have a nice weekend!

Removing orphan DataPump jobs and filtering included tables with a query

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:

drop table YOUR_JOB;

If the database is using a recycle bin the table has been renamed to a name starting with 'BIN'; if you rerun the query above the job is renamed to reflect this, but if you purge the recycle bin the job will be finally removed:

purge recyclebin;

If your PL/SQL code fails during definitions before you do a detach you cannot do a second attach. Since you have no reference to the job the only solution I'm aware of is to reconnect (i.e. create another database session). Better yet is to define an exception on ORA-39001 and retrieve the error message with dbms_datapump.get_status, and dbms_datapump.detach(l_handle) in the exception handler:

declare
  invalid_arg exception;
  pragma exception_init(invalid_arg,-39001);
:
:
begin
:
:
exception when invalid_arg then
    dbms_datapump.detach(l_h);
end;

If you drop the master table that is in the defining state, you will not be able to attach to the job, but if you try to attach to such a job the job will be removed for the user_datapump_jobs table.

If you try to add a metadata filter of type NAME_EXPR and you add a subquery in the expression, make sure that any table name in the subquery is prefixed with table owner or else you may receive ORA-00942 in the logfile:

dbms_datapump.metadata_filter(handle => l_h, name => 'NAME_EXPR', value =>'IN (SELECT TABLE_NAME FROM SCOTT.CHANGE_TABLE)'  ); 

Typical error in logfile when schema is missing:

Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6307
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x7bfb80b0     15032  package body SYS.KUPW$WORKER
:
: