ORA-00600: Error in Query Oracle 12c Using WITH

ORA-00600

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 11.2.0.1 to the latest version 12.2.0.1.

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: [15815], , [12][10], [9], [], [], [], [], [], [], [], [], []

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, TRUNCATED(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, TRUNCATED(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 11.2.0.1 (version before the Upgrade) and did not change with the upgrade to the current version of the database (12.2.0.1).

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:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/OPTIMIZER_FEATURES_ENABLE.html#GUID-E193EC9E-B642-4C01-99EC-24E04AEA1A2C
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='12.2.0.1' 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.
Database Team

If you don’t want to miss any of our publications, subscribe to GPS Open Source News

Comments are closed.