The system I'm working with didn't used to use global temporary tables for anything. Instead we have a buttload of throw-away tables floating around. My current project would ordinarily add to that, not just once, but twice. Yuck!
So I thought I'd see if I could get around the need for throw-away tables by creating a standardized global temporary table. That, at least, seems to be what those things are designed for. But whenever I run my query it crashes: 00600, [2865], [1025], [1024], [61693]
I have no idea what the numbers mean. More to the point, when I replace the global temporary table with a regular table the query runs fine. The problem also seems to be related to the size of the global temporary table, since a small dataset doesn't cause the crash. I've also found that the problem is related to calls to a PL/SQL function in the query, since I can avoid the crash by removing those calls from the query.
So. Is it possible to get around this problem? Is there, perhaps, some parameter I should use when defining the global temporary table?
You don't mention what Oracle release you are using, but i'm guessing 8.1.7 or older? If so, what you are seeing is a bug when scanning global temp tables, which is fixed in 8.1.7.1 patch.
You don't say your database platform nor the database release. Anyhow, on Metalink you can find some bug descriptions that result in ORA-600 [2865]. One of them (bug no. 1530519) is directly related to global temporary tables and affects release between 8.1.5 and 8.1.7.0. It is supposed to be fixed in 8.1.7.1 and above.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Originally posted by hmoulding Oops. Sorry. 9.2.0.2, I think. Better than 9 for sure.
Nevermind. Apparently it is 8.1.7. The person I asked thought I was talking about a different application. So the problem is identified. Thanks for all your help!
Originally posted by waitecj Is there an echo in here?
Ooops, sorry, I swear I haven't seen your reply when I was writing mine. It must have taken me more than 15 minutes from reading the original post, performing a search on the Metalink and writing my answer.
If I knew you would provide an answer I wouldn't bother to investigate the isue. But anyway, I belive we both (all) learned something new. Or perhaps more precicely - we all became aware of yet another bug.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Originally posted by jmodic Ooops, sorry, I swear I haven't seen your reply when I was writing mine. It must have taken me more than 15 minutes from reading the original post, performing a search on the Metalink and writing my answer.
To be honest, your explanation made more sense than mine anyway so its a good job you did make the effort
Global Temporary Table Causes ORA-00600
Reply With Quote
Bookmarks