1 2 Previous Next 23 Replies Latest reply on Jul 18, 2017 4:31 PM by jgarry

    ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]

    user10690927

      Please help how do I resolve this issue, at least a workaround.

       

      We have a monthly aggregation job which runs every 1st of the month to aggregate data from a previous month, and insert into a table. But I'm encountering this error, and the batch job will not continue:

       

      ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]

       

      The the tables related are partitioned. (insert into <<MONTH table>> select from <<DAY table>>)

       

      Our version of Oracle is 11.2.0.4.0, and it is using RAC with 5 nodes.

       

      The error disappears if I set the parallelism of both tables to 1, but the job will finish in 7 days which is very unacceptable. In the past, it only takes around 3-4 hours. At the moment, the DAY table and MONTH tables have both the degree of parallelism set to 32.

        • 1. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
          AndrewSayer

          Ora-600 means go straight to oracle support.

          There is a handy ora-600 lookup page you can use

          • 2. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
            user10690927

            Unfortunately, I don't have access to Oracle Support as I work with 3rd party company.  Maybe there is a workaround.

            • 3. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
              user10690927

              I added new information:

               

              "The error disappears if I set the parallelism of both tables to 1, but the job will finish in 7 days which is very unacceptable. In the past, it only takes around 3-4 hours. At the moment, the DAY table and MONTH tables have both the degree of parallelism set to 32."

              • 4. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
                AndrewSayer

                user10690927 wrote:

                 

                Unfortunately, I don't have access to Oracle Support as I work with 3rd party company. Maybe there is a workaround.

                Any workaround would be from Oracle support and for anyone with support to divulge the information to you would be a breach of their agreements with Oracle.

                 

                My advise, figure out what's special about what you are doing and stop doing it. The error attributes suggest something about writing to non existent partitions so consider what that could mean

                • 5. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
                  user10690927

                  We didn't do anything, so there is nothing to stop. The job runs automatically. And also, the partition exists as it is also automatically created by the application. I have verified the partition, and it exists and is writeable.  It has been like that ever since, but this error appears recently. Weird thing is, the DB has not been touched nor installed with anything. I think this error "full slot" has something to do with the hash table, or something else, not with the physical partition. The execution plan shows a TABLE SCAN with HASH GROUP BY.

                  • 6. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
                    AndrewSayer

                    user10690927 wrote:

                     

                    We didn't do anything, so there is nothing to stop. The job runs automatically. And also, the partition exists as it is also automatically created by the application. I have verified the partition, and it exists and is writeable. It has been like that ever since, but this error appears recently. Weird thing is, the DB has not been touched nor installed with anything. I think this error "full slot" has something to do with the hash table, or something else, not with the physical partition. The execution plan shows a TABLE SCAN with HASH GROUP BY.

                    I don't know.

                    Oracle support are the only people that can help you really. although I don't believe that nothing has changed.

                     

                    BTW 3 to 4 hours is pretty slow even for bulk processes, are you sure you're not doing things row-by-row or doing unnecessary chat with other processes?

                    • 7. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
                      user10690927

                      Hey, if you don't want to believe me, I have nothing to say. You keep on insisting we did something. My God, do you have the power to see things we don't see? What the hell! :-)

                       

                      The DAY table is 1.6TB, and it has a retention of 60 days. You'll have an idea of the size of each partition that is being done with full table scan each month.

                       

                      ORA-0600 is surely a bug, but there are times even a restart of DB solves the problem. But this case, even if we restarted the DB, lowered the parallel processes, it's still the same error.

                       

                      Can you give more ideas other than saying it's Oracle Support who can solve this, and insisting that we did something?

                      • 8. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
                        AndrewSayer

                        user10690927 wrote:

                         

                        Hey, if you don't want to believe me, I have nothing to say. You keep on insisting we did something. My God, do you have the power to see things we don't see? What the hell! :-)

                         

                        The DAY table is 1.6TB, and it has a retention of 60 days. You'll have an idea of the size of each partition that is being done with full table scan each month.

                         

                        ORA-0600 is surely a bug, but there are times even a restart of DB solves the problem. But this case, even if we restarted the DB, lowered the parallel processes, it's still the same error.

                         

                        Can you give more ideas other than saying it's Oracle Support who can solve this, and insisting that we did something?

                        Im saying it's unlikely that you just hit an ora-600 by doing something you always do, something has usually changed. Has it always done a hash group by for example? Perhaps it used to do a sort group by but recently changed plans due to statistics or memory setting changing?

                         

                        Yes, Ora-600 is a bug with the Oracle kernel, that's why the solution lies with purely with Oracle. Any knowledge anyone would have on the bug would be through Oracle support, and it is strictly against the support contract to share this information. So no-one can really help you that much.

                        • 9. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
                          mtefft

                          > You keep on insisting we did something

                           

                          No, he did not. He said: "I don't believe that nothing has changed."

                           

                          "Something has changed" could be:

                          • You changed something
                          • Another developer or DBA changed somehtng
                          • Statistics changed
                          • Workload changed
                          • A patch or PSU was applied
                          • AMM decided to give more memory to SGA than PGA or vice versa

                           

                          But sometimes we can never find 'what changed'.

                          Nobody except Oracle will really be able to help you with anything specific on this. But, if that is not an option, there is detective work you can do yourself. As Andrew suggested in an earlier comment, try to glean meaning from the message about nonexistent partitions. Try tweaking different versions of the query and see if it goes away.

                           

                          It's not a useless skill. I can tell you: sometimes you go to look up an ORA-600 error in MOS - and there is nothing there. So you would be back to doing detective work anyway while you wait for an SR...

                          • 10. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
                            user10690927

                            The reason why I'm reallly sure that we did nothing because this system will be migrated to Hadoop pretty soon :-) This DB has not been "touched" nor upgraded for many years now, even applications are no longer upgraded because of the planned migration. The last_ddl_time of all the procedures/functions are still 2015, so basically nothing has changed. But in the meantime while Hadoop is not yet ready, I'll have to live with this platform and fix problems that go with it. If I can't, then I'll have no choice but to raise this up with Oracle Support through our customer. I just tried my luck if anyone has encountered this problem, and could share what he/she did.

                             

                            I haven't checked the execution plans in the past of this particular job. Unfortunately, our AWR's retention is only few weeks.

                            • 11. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
                              user10690927

                              I'm not in the liberty to make new versions of the query because the application is from another 3rd party. I can only simulate it by copying all the pertinent tables and change the names, modify the query, and then run it. That's another way of trying to fix it while waiting for the SR to be opened with Oracle Support. As I've mentioned in the earlier post, I don't have any access with MOS, so I'll have to ask the customer to create it on my behalf.

                              • 12. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
                                AndrewSayer

                                user10690927 wrote:

                                 

                                The reason why I'm reallly sure that we did nothing because this system will be migrated to Hadoop pretty soon :-) This DB has not been "touched" nor upgraded for many years now, even applications are no longer upgraded because of the planned migration. The last_ddl_time of all the procedures/functions are still 2015, so basically nothing has changed. But in the meantime while Hadoop is not yet ready, I'll have to live with this platform and fix problems that go with it. If I can't, then I'll have no choice but to raise this up with Oracle Support through our customer. I just tried my luck if anyone has encountered this problem, and could share what he/she did.

                                 

                                I haven't checked the execution plans in the past of this particular job. Unfortunately, our AWR's retention is only few weeks.

                                You may not have done anything, but there are plenty of automatic things can Oracle will do in the background that change things (stats gathering, memory management), this is why it's always a good idea to have a bench mark (but I appreciate that it's not always an easy case to make until it's too late).

                                 

                                If the exception consistently occurs, look at the full execution plan outline (using the all formatting option in dbms_xplan) and look at what could be the cause. The hash group by could be a suspect so identify the line in the outline causing it and try hinting the opposite (e.g. it might say hash_gby(@sel$5 so you'd hint no_hash_gby(@sel$5)). Other things you could try would be to try decreasing the optimizer_features_enable parameter for the session running the query (or via the opt_param hint) but note that this could cause bugs of it's own (unlikely but always worth testing).

                                • 13. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]
                                  jgarry

                                  Does the process drop partitions?  Sometimes you can get strange things happening after doing some odd pattern like dropping every other object in a tablespace.  New objects fail because they eventually try to cross a mysterious internal boundary.  Extra space can sometimes be used unexpectedly with parallel insertion - both from bugs and the way things work.

                                   

                                  Interestingly, one of the bugs found for this error has a workaround: set parallelism to 32.  No way to avoid Oracle support here.

                                  • 14. Re: ORA-00600: internal error code, arguments: [32693], [qesh no partition to write (with full slot)]

                                    We didn't do anything, so there is nothing to stop. The job runs automatically. And also, the partition exists as it is also automatically created by the application. I have verified the partition, and it exists and is writeable. It has been like that ever since, but this error appears recently. Weird thing is, the DB has not been touched nor installed with anything. I think this error "full slot" has something to do with the hash table, or something else, not with the physical partition. The execution plan shows a TABLE SCAN with HASH GROUP BY.

                                    Ok - so read the above several times.

                                     

                                    See how you keep 'telling us' what you did, that 'some' partition exists, that it is created automatically, that you verified the partition, it is writeable, and that you have an execution plan.

                                     

                                    See how you haven't SHOWN US ANYTHING? You haven't posted ANY query, stats, table/index ddl, execution plan - NOTHING!

                                     

                                    If you want help with a problem you need to SHOW US:

                                     

                                    1. WHAT you do

                                    2. HOW you do it

                                    3. WHAT results you get

                                     

                                    Something changed or Oracle would keep acting the same way it always did. If Oracle is now acting differently it is because SOMETHING CHANGED.

                                    1 2 Previous Next