Re: Weird Bug: One schema accessing another schema's data

Yes, I've seen something similar to this occur with Oracle 8.0.5 on an Alpha server. In that case, it was the same SQL statement being invoked within a PL/SQL package, but with different synonyms from different users to point to different tables. This started occuring when we upgraded to 8.0.5 from 7.3.4 so it seems specific to that release. I suspect that the package is cached and somehow Oracle is getting confused and reusing the parsed SQL code before or without checking the synonyms first. It was inconsistent and seemed to occur only under very high transaction volumes. The only workaround we could find to work was to hard-code the schema name and use different packages.

We were not able to reproduce it for Oracle support and we needed to fix it quickly as it was affecting a major production system.

Marc Perkowitz
MTP Systems Consulting, Ltd.

In a message dated 7/30/00 10:18:45 PM Central Daylight Time, leng.kaing_at_hancorp.com.au writes:

<< Hi Guys,  

 Environment: Oracle 8.0.5 on HP 10.20  

 When a colleague asked me about this the other day, I said that it's not  possible. But now that I've seen it, I'd like to know if you have seen it  too.  

 What happened was that in the one instance we had 2 schemas - B1INT and  B1DEV. These are our integration testing and development schemas. They are  self-contained. There are NO synonyms, grants etc pointing from one schema  to the other. What happened on Friday night was that when a stored package  was executed in the B1INT's schema, it tried to access a table from the  B1DEV schema. The error generated was ORA-6512. No trace dumps or other  errors were generated. Within the database I saw that the application was  connecting as B1INT, and I also ran a trace of the session looked "normal"  to me. There were NO schema switches, or references to B1DEV from B1INT.  Also, there was data in the B1INT's table, but no data in the B1DEV's table.  And the application complained about no data in the B1DEV schema.  

 The only resolution was to flush the shared pool! Once this was done, the  application found the data and continued in its merry way.  

 So, has anyone seen this problem? Got a work around etc. I haven't tried  explaining it to Oracle Support for fear that they are not going to believe  me and demand that I repproduce the error. The problem happens very rarely  and is not reproducable on demand so I'm afraid I don't want to put up with  the off-handed "call us next time it happens" response. Received on Sun Jul 30 2000 - 22:41:55 CDT