rmission
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
SELECT *
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
SELECT *
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.
Vlad.
So it's definately a problem with synonyms...
You can confirm this by logging in using SQL*Plus as BLOW and doing
SELECT *
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
SELECT *
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.
Vlad.
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','
'synonym '||decode(owner,'PUBLIC',n
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||
|| ' ;' 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.
Thanks.
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','
'synonym '||decode(owner,'PUBLIC',n
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||
|| ' ;' 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.
Thanks.
ASKER
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.
vlad_impala,
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.
junfeb,
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.
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.
vlad_impala,
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.
junfeb,
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.
ASKER
junfeb,
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.
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!
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!
junfeb,
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.
Vlad.
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.
Vlad.
vlad,
Thanks, I appreciate your thought and kind words. But, No Thanks! to the points.
Junfeb.
Thanks, I appreciate your thought and kind words. But, No Thanks! to the points.
Junfeb.
ASKER
junfeb,
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.
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.
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.
Vlad.