can anyone tell me how something like this can happen. i have a package that was created under user a, and executed under user b. the package writes to table c.
i have tow guys looking at table c after the process finished, one of them excuted the process and the other one not. they bouth enter the databes as user b. when they look at table c, the one that ren the process can see the new row, the other one can not. only if he reconnects to the database he can see the new row!
It could be a reason when the first b had connect to the process the commit has not propagated to the database level, and when the second user connects it would have got. Some times if it was user b1 is doing the insert, then he would be able to see the inserted new data, b'cos the session hasn't performed the commit on his session. On other words the data is still sitting on the user's RBS, which is why the user who inserted the data was able to see. Until he does and explicit commit, or he exits the session when the commit is performed when there is no explicit commit, the other user b wouldn't be able to see the new change. Hope this would fit your problem.
On such cases, there seems to be some kind of lock being held on the table that prevents the user from looking into it. When he reconnects, it sees to get resetted. This would be my assumption. Other thing would be that you might not have the parallel querry option on this table.
First of all could you, tell me what is your Oracle version. and platform. Secondly, go ahead and check the DBA_LOCKS, V$LOCKS, V$LOCK_ACTIVITY , V$LOCKED_OBJECT and V$LOCKS_WITH_COLLISIONS to see what is exactly happening.
On 8I, parallel query will be enabled by default. In the mean time post, on what you see.
[QUOTE][i]Originally posted by sambavan [/i]
[B]On such cases, there seems to be some kind of lock being held on the table that prevents the user from looking into it. When he reconnects, it sees to get resetted. This would be my assumption. Other thing would be that you might not have the parallel querry option on this table.
Hope this would shed some light.
AFAIK in Oracle there is no way any kind of lock could prevent users to read the locked table. "Readers newer block writers, writers never block readers..." is Oracle's well known sintagma.
So you'd better not look in that dirrection, the problem must be somewhere else.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?