Hello, today we want to share an ORA-00600 error that has arisen in a client that shows how important it is to know the new functionalities of the application with which we work, in this case the Oracle database.
How did the ORA-00600 error appear?
In one environment, a migration was made from a database from version 126.96.36.199 to the latest version 188.8.131.52.
After performing the migration, we encounter an ORA-00600 error in a query. This query has been used by the application served by the database for quite some time.
The error that was obtained is:
ORA-00600: internal error code, arguments: , , , , , , , , , , , , 
Examination of the query
The query is written using the WITH clause, and its syntax is as follows:
WITH SAWITH0 AS (select sum(T22.CARGA_DOC_ID) as c1, TRUNC(T31.DAY) as c2 from GRUPO_GR.TD_2_TIEMPO T31, GRUPO_RH.TF_CC_ORI_EMPLE T22 where ( T31.DAY_ID = T22.DAY_ID ) group by T31.DAY_ID, TRUNC(T31.DAY) having 0.0 < sum(T22.CARGA_DOC_ID)) select D1.c1 as c1 from ( select distinct D1.c2 as c1 from SAWITH0 D1 order by c1 desc ) D1 where rownum <= 1
After testing to find the reason for the error. We realize that the problem was in the use of the WITH clause. When using its contents in the FROM clause, the query works correctly:
select D1.c1 as c1 from ( select distinct D1.c2 as c1 from (select sum(T22.CARGA_DOC_ID) as c1, TRUNC(T31.DAY) as c2 from GRUPO_GR.TD_2_TIEMPO T31, GRUPO_RH.TF_CC_ORI_EMPLE T22 where ( T31.DAY_ID = T22.DAY_ID ) group by T31.DAY_ID, TRUNC(T31.DAY) having 0.0 < sum(T22.CARGA_DOC_ID) ) D1 order by c1 desc ) D1 where rownum <= 1
It doesn’t seem to be a version issue. Queries with the WITH clause are allowed since at least Oracle version 9i.
Of course, we contact Oracle through the support page, to resolve the doubt we have with the error, since these ORA-00600 errors are the least documented errors and it is difficult to track and solve them.
After several exchanges of information, it is concluded that the problem is that the parameter optimizer_features_enable continues with the value 184.108.40.206 (version before the Upgrade) and did not change with the upgrade to the current version of the database (220.127.116.11).
Reviewing the Oracle documentation we see that this parameter enables a series of characteristics of the operation of the optimizer, which change in each version, and it is clear that we have found one of them. The characteristics of each version can be checked at the following link:
After reviewing this, the parameter is changed to the new version, and can be done online, with the ALTER SYSTEM instruction:
ALTER SYSTEM SET optimizer_features_enable='18.104.22.168' SCOPE=both
And the query begins to work, returning a result:
C1 ---------------------- 31/07/2018 00:00:00
We hope you liked it, we leave you our entries about Oracle in case they are also useful.