Re: corrupted table?

Gumuz wrote:

> so once in a while, i've this strange behavior in 'some' tables,
> 'sometimes'. sometimes a simple 'select *' doesn't even work. recently i
> got the problem that a select was working fine. but when i used one of the
> columns in a join, it messed up, got stuck, had to cancel.

Oracle errors messages? Any traces or core dumps? What does the alert log say?

> now in all these cases i could solve the problem by dropping and
> recreating the table with the original data put back in. then it works....
> for a while. it seems that some tables have the tendencie to give this
> error more then others, but then again... not always.

Are these tables on the same tablespace? Or different tablespaces that uses datafiles on the same filesystem?

Never seen anything like that in Oracle myself.

If I do, I will first check the V$ event and wait tables for that session that seems to be hanging. Also checking for locks/deadlocks issues. This should give me an idea of why it seems to be hanging. If not, then I will try and confirm the error by doing (while the process seems to hang), another SQL select on that same table. But doing something very simply like a 'SELECT * FROM foo WHERE rownum = 1'. Basically trying to determine if all access to the table in question results in a hang. If not, then I would think it must be something with the query or query shadow processes itself.

There's a lot of info that's pretty useful in the V$ tables. Also always remember to check the logs.

It also could be an operating system issue. Once had a problem with a kernel bug that failed to deliver async i/o completion events. Thus we had Oracle processes that were forever waiting for an async i/o to complete. We only managed to discover this by truss'ing the actual Unix Oracle process that seems to be hanging.

What I would not do however, is simply an export, drop table, and import. That is fixing the symptom. Not the disease. :-)

--
Billy