Hello gurus, after upgrade our clients for oracle 11 we start getting aleatory "ora-00942: table or view does not exist." on the application, this happens ever day since upgraded, just finished upgrade, and some clients start getting this error once a day at least 2 or 3 clients get this errors.
the oracle server is 220.127.116.11 running on hpux 11.31. clients had oracle 9 run time installed before and was uninstalled and installed the new client oracle11.
looking at alert log we don´t have any problem on database side. the table that error tells is avaiable , i disabled and enabled constraints and it back to work immediatly.
so far i have no idea if there is something that i can do on database side, or this is something related to oracle client, application or what ever could be.
application log is always the same always sql error: ORA-00942. but tables always there, and avaiable via sqlplus or pl/sql. so far no clue i keep doing disable/enable constraints and all back to work for couple of time.
please send me your ideas or experiences.
thanks a lot for your help.
What table are we talking about? User schema table, system table? Does it have a synonym?
ORA-00942 can occur when invoker does not have direct privileges on underlying table.
Role security changed in Oracle 11. To test try Granting direct to a user instead of using a role.
Hello thanks to reply,
it´s a user table like schema.table
the question is that is not always the same table
some times is schema.tableA, another time is schema.tableB, or schema.View_C
the database was oracle 9 i upgraded to oracle 11 i think 4 months ago and i never get this problem before, just after upgrade the clients also to oracle11.
before upgrade as a oracle document suggest i explicity grants to the users in connect role that is different in oracle 11 also. so far no problem just now after clients upgraded to 11.
but the funny is the error happens on both kind of clients now, not only at oracle11 clients but there are some still on oracle9(not have time to upgrade all more then 100) that report the error :(
but is a good idea to grant all to all objects again to see if it help, can you help me with sql for this ?
Looks very relevant we use jdbc and we have multiple schemas all belongs to the same application and each schema is for a different mill application, and they´re all identical with identical tables.
any workaround ?!??!?!
Hello thanks for reply
i checked with an application person and he said they don´t use jdbc drivers so even the description very closer with problem this should not be our case.
the clients have oracle 11 installed with tnsnames configured to our db, but no jdbc conections they said.
let me know any other ideas then.