Global Temporary Table Causes ORA-00600

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.

Is there an echo in here?

;)

Quote:

---

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.

Quote:

---

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!

Quote:

---

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. ;)

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

Thanks
Tamil

Quote:

---

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 :)