Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ora-00600 with union all

netikrasFeb 7 2018 — edited Feb 8 2018

Hi,

so today I've whitnessed something new. After doing some pen-testing on the application I found the database damaged in some way.

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

pastedImage_0.png

after this error pops up JDBC connection becomes broken and cannot be reused. Connection does not break if I'm running this via sqlplus.

This happens ONLY when (there is union all in the query) && (there are 2 or more columns) && (there is a count(*)) && (there is order by clause). Skip either of the components and the query executes just fine. Happens with any view/table I have tried.

Noticed this first on a view using union all in its query as java app started spitting out errors suggesting DB connection has been closed prematurely.

Does that mean there's no way out but a fresh database?

---

Some food for google's cybermonkeys to fiest on:

select count(*) from (

select

**0 as id1,**

**1 as id2**

from dual

union all

select

**0 as id1,**

**1 as id2**

from dual

order by id1 desc

)

;

ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], [], [], [], [], []

00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"

*Cause: This is the generic internal error number for Oracle program

       **exceptions. It indicates that a process has encountered a low-level,**

       **unexpected condition. The first argument is the internal message**

       **number. This argument and the database version number are critical in**

       **identifying the root cause and the potential impact to your system.**
This post has been answered by Joerg.Sobottka on Feb 7 2018
Jump to Answer

Comments

Joerg.Sobottka Feb 7 2018 — edited on Feb 7 2018

Hm, maybe you should patch your database to 11.2.0.4 - you have a real old version.

Nevertheless, have a look at the alert.log, there you will find more information about the ora-0600 (e.g. trace files). With the Information there you can have a look at: ORA-600/ORA-7445/ORA-700 Error Look-up Tool (Doc ID 153788.1)

Mark D Powell Feb 7 2018 — edited on Feb 7 2018

netikras, if you have access to Oracle support or someone from your site does the following support document should be of interest.  There are a couple of bugs that may be of interest.
- -
ORA-600 [qctcte1] (Doc ID 248095.1)
- -
HTH -- Mark D Powell --

PS - I would remove the order by from the subquery.  Being you are getting a count why would it even matter to the result?

netikras Feb 7 2018 — edited on Feb 7 2018

Heyy, thanks for a lightning-fast reply!

However... I don't think anyone is interested in upgrading the database version for my project It's quite an old project and its managers are... well... let's say "not very invested into project's tech side" So I just gotta keep the show on the road with whatever I have.

The alert.log file does not have a lot to say. And the trc files is something I don't think I'll live long enough to understand The link you gave requires an account eligible for ORA support (which I don't have.. hence I'm here, in a community forum)

[root@ora ~]# tail /u01/app/oracle/diag/rdbms/project1/project1/trace/alert_project1.log

Trace dumping is performing id=[cdmp_20180207162723]

Wed Feb 07 16:27:47 2018

Errors in file /u01/app/oracle/diag/rdbms/project1/project1/trace/project1_ora_27543.trc  (incident=11303):

ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

Wed Feb 07 16:27:48 2018

Sweep [inc][11303]: completed

Sweep [inc2][11303]: completed

Wed Feb 07 16:27:49 2018

Trace dumping is performing id=[cdmp_20180207162749]

[root@ora ~]# ll /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

-rw-r----- 1 oracle dba 3070656 Feb  7 16:27 /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

[root@ora ~]# less /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

[root@ora ~]# du -sh /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

3.0M    /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

[root@ora ~]# wc -l /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

57784 /u01/app/oracle/diag/rdbms/project1/project1/incident/incdir_11303/project1_ora_27543_i11303.trc

[root@ora ~]#

netikras Feb 7 2018 — edited on Feb 7 2018

Nope, I have no access to ORA support :/ And regd the count...

select count(*) from my_awesome_view;

and the view uses the order by.

Joerg.Sobottka Feb 7 2018 — edited on Feb 7 2018
Answer

There is a bug with union all statements Bug 9826065  ORA-600 [qctcte1] from UNION ALL with identical expressions in select list).

It is already fixed, but as you don't want/can't upgrade the database you can try to use the workaround.

 Set "_optimizer_cost_based_transformation"=off or Set _fix_control='7597159:off'
Marked as Answer by netikras · Sep 27 2020
John Thorton Feb 7 2018 — edited on Feb 7 2018

netikras wrote:

Nope, I have no access to ORA support :/ And regd the count...

select count(*) from my_awesome_view;

and the view uses the order by.

SELECT COUNT(*) returns a single scalar value so there is NOTHING to ORDER BY!

What is wrong order for single row results?

netikras Feb 7 2018

I know there is nothing to order in scalar. But there is smth to order in a view. And I'm counting rows appearing on the view.

pastedImage_1.png

netikras Feb 7 2018

So the first setting did not help me at all. The second one however seems to fix whatever the issue is this (_fix_control='7597159:off'). Is it safe to use? What am I losing by disabling this 7597159 (fix?) ?

Mark D Powell Feb 7 2018

netikras, you are going to lose a specific query transformation by the CBO that may result in some other queries degrading.  If any other queries degrade or not can likely only be determined by heavy testing.  If possible, you should check to see if this parameter can be set at the session level then set it only in those cases where the error is encountered.

- -

Try comparing the before and after (no fix, fix applied) plans for the SQL to see if you can spot the transformation.

- -

HTH -- Mark D Powell --

SeánMacGC Feb 7 2018

Does this also error (aliasing the columns in anything but the first UNION (ALL) block is unusual, and not useful at all):

select count(*) from (

  select

    0 as id1,

    1 as id2

  from dual

union all

  select

    0,

    1

  from dual

);

netikras Feb 8 2018

What the hell....? It worked. It really worked! Why did it work? And what does that mean? And what's *wrong* with aliasing columns in union block?

AndrewSayer Feb 8 2018

netikras wrote:

What the hell....? It worked. It really worked! Why did it work? And what does that mean? And what's *wrong* with aliasing columns in union block?

It doesn’t have an order by clause.

I find it confusing that you are pen testing an out of support version, there is going to be bugs and there is going to be security holes.

If you have little control over patching, then just make sure your application doesn’t call SQL like you’ve shared, that should be easy as it will fail testing anyway

netikras Feb 8 2018

Oh, right.... Thanks. I'm still working on my morning coffee so I might overlook obvious things And the qry does fail with order by clause.

interestingly enough the union all thing was NOT failing before pen-testing the app. I guess I'll have to remodel the view anyway

Joerg.Sobottka Feb 8 2018

netikras wrote:

What the hell....? It worked. It really worked! Why did it work? And what does that mean? And what's *wrong* with aliasing columns in union block?

This is, why it is called a bug and fixed at a later Oracle database version ;-)

Bugs are NOT necessarily logical.

Please mark your question as answered, if you don't need any more help.

netikras Feb 8 2018 — edited on Feb 8 2018

Thank you. I've marked your answer as the correct one.

The last question on this note... How do I configure the DB to disable that particular fix by-default? I know I can disable it once DB is up using 'alter system <...>', 'alter session <...>'. Is there a way to make the DB to start up with the fix already disabled? [yes, I do know it's a bad idea and yes I do intend to shoot my own foot if anyone just could explain HOW ]

Joerg.Sobottka Feb 8 2018

If you run with a spfile you can just add the scope to your alter system (as default scope is MEMORY only), example:

ALTER SYSTEM SET "Underscore_Parameter"="XXX" SCOPE = SPFILE;

ALTER SYSTEM SET "Underscore_Parameter"="XXX" SCOPE = BOTH;

If you run with a pfile you can just add the parameter like every normal parameter to your init.ora, example:

_optimizer_autostats_job=false

unknown-7404 Feb 8 2018

How do I configure the DB to disable that particular fix by-default?

You don't.

You write your code so that the bug doesn't occur.

1 - 17
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 8 2018
Added on Feb 7 2018
17 comments
4,436 views