Error Code 'ORA-00942:Table or view does not exist'

This is urgent, and I will appreciate very much your prompt reply.

When I am connecting to my Oracle DB, I get this error:
'ORA-00942: Table or view does not exist'.

Some backgroud information -
I have Microfocus Cobol as the front-end to my Oracle7 database. Recently, I started to help with the administration of the Oracle database. A few days ago I created a new user (say for this illustration, user BLOW). After creating user BLOW, I granted him these privileges: CREATE SESSION (so he can connect to my database) and SELECT ANY TABLE (so he can select from any table).

From within my Cobol program and on runtime, I am accepting both the Oracle username and the Password from the user and then use these values to connect to the Oracle DB.

My problem is when I connect as this new user (BLOW), I am getting the error above (i.e. 0RA-00942: Table or view does not exist) and I am not able to connect to the database. I just can't figure out why this error is coming up. I can connect to the database without any error (via my cobol program) when I connect as a user which was created previously by someone else (let's say this other user created by someone else is user BLACK). I checked out the roles granted to user BLACK and there were 2: CONNECT and RESOURCE. So I granted these roles to BLOW - but I still ended up with the same error when I connected as BLOW after granting the 2 roles.

Please, this is urgent. Will dearly appreciate your prompt reply.

Who is Participating?
vlad_impalaConnect With a Mentor Commented:
You basically cannot see the tables you need to see - it all boils down to SYNONYMS, ROLES and PRIVILEGES on the tables.
1) Look to see if the other user has any private synonyms i.e. for the user accesses, if he does set the same synonyms up for BLOW.  If there are no private synonyms BLACK must be using public synonyms, or BLACK is the table owner.
If public synonyms exist for the tables make sure BLOW has no private synonyms that would conflict with them.

2) As the user BLACK has only two roles, the permissions on the tables have not been granted via a role.  Therefore BLACK has either, a) been granted privileges directly by the table owner or b) is using privileges granted to PUBLIC by the table owner or c) BLACK is the table owner.
To fix each scenario -
a) as the table owner, grant the same privileges to BLOW as have been granted to BLACK.
b) this should be fixed by the creation of private synonyms (if BLACK was using public synonyms and privileges had been granted to PUBLIC you would have had no problems).
c) as BLACK grant the appropriate privileges (select, update, insert, delete) to BLOW.

If you need any more help post a comment.

Basically BLOW needs ...
1) a synonym for the table - this can be a private synonym or a PUBLIC synonym.
2) the appropriate privileges on the table - either granted directly to the user, or granted to PUBLIC, or granted to a role which is then granted to the user.

The easiest way to make sure all the privileges are granted is to set up a role.  Granted the necessary privileges to the role and grant the role to the users that need access to the tables.  When you then change the privileges for the role the users automatically have the same privileges.

Ooops - I have just re-read the question and realised that I hadn't spotted the fact you have granted SELECT ANY TABLE to BLOW...

So it's definately a problem with synonyms...
You can confirm this by logging in using SQL*Plus as BLOW and doing
FROM table_owner.tablename;

where tablename is the name of the table and table_owner is the owner of tablename.
If you get data back then you can definately see and select from the table.
You then need to create a synonym thus (whilst logged in as BLOW)
CREATE SYNONYM tablename FOR table_owner.tablename;
this will allow you to do
FROM tablename;

Alternatively as SYSTEM you could do
CREATE PUBLIC SYNONYM tablename FOT table_owner.tablename;
and then any user would be able to the table without preceedinging it with the owner.

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Here are some scripts that will help you identify the privs and help you with the recreation.

This will help you build any synonyms if they are missing. This will generate a script of synonyms . Please run this as sys. You can tweak this script to generate a script to create synonyms by changing the owner.

spool cre_syn.sql
select 'set echo on' from sys.dual ;
select 'spool cre_syn.lst ' from sys.dual ;
select 'create '||decode(owner,'PUBLIC','public ',null)||
'synonym '||decode(owner,'PUBLIC',null,owner||'.') ||
 lower(synonym_name) || ' for ' || lower ( table_owner ) ||
'.' || lower ( table_name ) || decode ( db_link, null,null,'@'||db_link ) ||';'
from sys.dba_synonyms
where table_owner != 'SYS' order by owner ;
select 'spool off' from sys.dual ;
spool off

Here is a scripts to help you identify table privs.

spool cretabpr.sql
select 'set echo on' from dual;
select 'spool crtabpri.lst' from dual;
select 'grant '||privilege||' on '||owner||'.'||
table_name||' to '||grantee||
decode( grantable,'YES',' with grant option ','NO',null) || ' ;'
from sys.dba_tab_privs  order by owner, table_name;
select 'spool off' from dual ;

HEre is one to identify the privs of a role -
spool crerltab.sql
select 'set echo on' from dual;
select 'spool crerltab.lst ' from dual;
select 'grant '||privilege|| ' on ' ||owner||'.'||table_name|| ' to ' ||role
|| ' ;' from role_tab_privs
select 'spool off' from dual;

After checking through all this, if you still have a problem, I have had this happen to me before, when temporary tables were created by a different user and I needed to have access to it. I'll give more information on the scenario, if you don't fix your problem after this initial evaluation.

Since ORA-942 doesn't tell you which table name, this may be some intermediate tables.


rmissionAuthor Commented:
Hello my friends,

What more can I ask for, but say 'Thanks a million' to everyone who participated in answering my question.

Believe me, I wasn't kidding when I said that I needed an urgent response (just look at how many points Iam left with). You guys have just given me a 2 weeks vacation starting coming Monday.


Thankyou very much.  I used the CREATE PUBLIC SYNONYM command ('cause I have more users who will be interested in these tables) and now my problem is solved.


Thanks alot for the scripts. I will definitely need these since I have a number of tables for which I want to create public synonyms on. It's just a pity that I'm not not going to give you the points though [I guess vlad_impala was just a second too fast :) ]

Thanks again to both of you.

A million thanks but no points eh! Not even for the scripts that you're going to be using.
rmissionAuthor Commented:

Sorry, but bellieve me, I am left with very little points at the point. I can buy some extra points (but the detail of going thru that is a bit too much for me) so once again, sorry for not allocating you any points.

However, (if I get you correct, and you really expect some points for your COMMENTS), I think that points are only awarded to the expert who proposes the answer first and this proposed answer is regarded as correct by whoever asked the question.
I don't even get any choice of splitting points (or is there a way around this). Furthermore, EXPERTS-EXCHANGE are the ones who are allocating the points, not me. I only say if an answer I got is correct or wrong (rejected). Please also note that you were giving your reply as a comment, not as an answer.

On a passing note, I can ignore your scripts (although I said earlier that I would be needing them), if this is what you want. But again, I guess it's hard for you to believe me.

Sorry, but if you still insist on me giving you some points, then please bear with me - as I said, I don't have enough points at the moment (in fact, I have got only 13 to use now) and it will take some time for my points to grow (5 per day, as you know).

Hope I have made myself clear.
Go Ahead and use the scripts! That's what I put them there for!
Do keep your points, You'd need them for other questions.
Yes! you can split points. Ask Customer service.
>Please also note that you were giving your reply as a comment, not as an answer
Bad choice I guess. Only because I wanted to help, I'd been in a similar situation.
>(if I get you correct, and you really expect some points for your COMMENTS)
Well why not, if the comment is worth your while.
Have a Good Day!


I would be happy to post a question for you to answer for 55 points in recognition of your contribution to this and other questions and the knowledge I have picked up from your answers/comments.  It will contain a question, which I am sure you will find easy to answer, and I will only accept an answer from yourself.

Leave a comment in this question if you are happy to proceed with this solution.



Thanks, I appreciate your thought and kind words. But, No Thanks! to the points.

rmissionAuthor Commented:

Please don't get me wrong. I didn't mean to be rude in any way.

Anyway I have learnt something from this. First (as you told me), I now know that I can and am able to split points. I will consider this point whenever I am posting any questions on this service in the future.

Secondly, in the future, I must also remember to give points to anyone who gives any comments that is worthwhile to me(although I am still unsure of how I can do this).

Have a good day too.
All Courses

From novice to tech pro — start learning today.