Global Temporary Table Causes ORA-00600
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Global Temporary Table Causes ORA-00600

  1. #1
    Join Date
    Jun 2005
    Posts
    3

    Unhappy 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?

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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.
    Assistance is Futile...

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Is there an echo in here?

    Assistance is Futile...

  5. #5
    Join Date
    Jun 2005
    Posts
    3
    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.

  6. #6
    Join Date
    Jun 2005
    Posts
    3
    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!

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Modic,
    You expressed your gentleness correctly. I liked it.

    Thanks
    Tamil

  9. #9
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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
    Assistance is Futile...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width