Easy way to crash a Oracle 10.2 session

This query crashes the session when it’s run or parsed.
I haven’t checked if this appears in other versions or not.

SQL> conn scott/tiger@orcl

SQL> select * from v$version;
Oracle Database 10g Enterprise Edition Release – 64bi
PL/SQL Release – Production
CORE Production
TNS for Solaris: Version – Production
NLSRTL Version – Production

SQL> explain plan for with q as
2 (select ‘x’ d from dual
3 union all
4 select ‘y’ from dual)
5 select d from q
6 union all
7 select d from q
8 order by dbms_random.value;
explain plan for with q as (select ‘x’ d from dual
ERROR at line 1:
ORA-03113: end-of-file on communication channel

The crashes happened when I added the “order by dbms_random.value”. The problem seems to be in the optimiser since I get the same results whether I run the query or just explain it. Possibly something to do with the optimiser wrongly assuming the random function is deterministic?

There’s a simple workaround, so I’m not too worried. I just change it to this and it works fine:

with q as
(select ‘x’ d from dual
union all
select ‘y’ from dual)
select d, dbms_random.value o from q
union all
select d, dbms_random.value o from q
order by o;

AUSOUG Conference 2007 Day 2
Will Oracle use my regexp function-based index?

Leave a Reply

Your email address will not be published / Required fields are marked *