Troubleshooting ORA-00942

Question

In the middle of an automated test run last night, one of my twenty worker threads blew out; the application was attempting an INSERT. Springframework reports ORA-00942: table or view does not exist.

Connecting to the database (11.1.0.6.0) through the enterprise manager, I pull up a SQL worksheet and query the table, discovering (a) that the table exists now (b) the row that should have been inserted is missing (c) the row previously inserted by this thread is present (d) that the table has rows before and after the error in time.

For a user with little oracle experience, how do I verify that the error originated with this database server, and from there how do I walk the error back to a root cause?

Answer 1

I would start as close to the database as possible, to confirm what SQL is executed, committed, rolled back etc. in the database itself. I'd set up TKPROF (check the docs, or visit Tom Kyte's site for more info) and see what actually gets executed from your app. That will at least help you to see if what you expect to be executed is actually "arriving" as it should.

An ORA-00942 will occur if the table is "invisible" to the calling programme i.e. if the permissions are not sufficient.

Answer 2

As Dave K said, the table may have existed, but became "invisible" to your process.

Because of the phrase "last night", my thoughts lead that there may be a nightly process, like a backup or clean-up process, which caused the table to be taken offline.