Global Temporary Table Causes ORA-00600

[hmoulding]

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?

[waitecj]

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.

[jmodic]

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.

[waitecj]

Is there an echo in here?

[hmoulding]

Originally posted by waitecj
You don't mention what Oracle release you are using, but i'm guessing 8.1.7 or older?

Oops. Sorry. 9.2.0.2, I think. Better than 9 for sure.

[hmoulding]

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!

[jmodic]

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.

[tamilselvan]

Modic,
You expressed your gentleness correctly. I liked it.

Thanks
Tamil

[waitecj]

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