Home » SQL & PL/SQL » SQL & PL/SQL » Ora-03113 on a simple query with a distinct and an Order by clause with DESC parameter specified (Oracle 11.2.0.1)
Ora-03113 on a simple query with a distinct and an Order by clause with DESC parameter specified [message #452951] Mon, 26 April 2010 09:57 Go to next message
ljobson
Messages: 5
Registered: April 2010
Location: Fort Worth TX
Junior Member
We have just upgraded several databases from 10g to 11g (11.2.0.1) x64 on AIX we are running PeopleSoft and we have a basic query that we are trying to run:

SELECT DISTINCT INSTITUTION, AID_YEAR, ACAD_CAREER, REL_ITEM_TYPE
FROM PS_PKG_REL_ITM_TBL
WHERE INSTITUTION='UTDAL'
AND AID_YEAR='2011'
AND ACAD_CAREER LIKE 'UGRD%'
ORDER BY INSTITUTION, AID_YEAR DESC, ACAD_CAREER, REL_ITEM_TYPE

the query generates the following error:

ORA-03113: end-of-file on communication channel Process ID: 8364148 Session ID: 71 Serial number: 4029

There is only one record to return, but the query fails and does not run. Coincidentally if we switch the last two fields in the order by clause the query will run.

We have checked the hidden parameters and the init file and everything seems to be correct. Has anyone seen this or know of a solution? We have a case with Oracle open but we have not gotten a solution.

Report message to a moderator

Re: Ora-03113 on a simple query with a distinct and an Order by clause with DESC parameter specified [message #452952 is a reply to message #452951] Mon, 26 April 2010 10:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
SELECT DISTINCT institution,
                aid_year,
                acad_career,
                rel_item_type
FROM   ps_pkg_rel_itm_tbl
WHERE  institution = 'UTDAL'
       AND aid_year = '2011'
       AND acad_career LIKE 'UGRD%'
ORDER  BY institution,
          aid_year DESC,
          acad_career,
          rel_item_type


Is AID_YEAR really a string? If so BAD "design"!

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Ora-03113 on a simple query with a distinct and an Order by clause with DESC parameter specified [message #452953 is a reply to message #452952] Mon, 26 April 2010 10:06 Go to previous messageGo to next message
ljobson
Messages: 5
Registered: April 2010
Location: Fort Worth TX
Junior Member
NO, aid_year is a field in the peoplesoft table ps_pkg_rel_itm_tbl. PeopleSoft like to make fields that normal people would make as numberic as varchar. Furthermore PS also likes to load a blank in every text field too, but that is not the question the questions is in 10g this query worked just fine now in 11.2.0.1 it does not.

[Updated on: Mon, 26 April 2010 10:07]

Report message to a moderator

Re: Ora-03113 on a simple query with a distinct and an Order by clause with DESC parameter specified [message #452954 is a reply to message #452951] Mon, 26 April 2010 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Generally speaking: ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support.

Rewrite your query in a way that changes the execution path.

Regards
Michel

Report message to a moderator

Re: Ora-03113 on a simple query with a distinct and an Order by clause with DESC parameter specified [message #452955 is a reply to message #452951] Mon, 26 April 2010 10:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It might be worth looking at which indexes the query uses when it fails, and rebuilding / recreating them

Report message to a moderator

Re: Ora-03113 on a simple query with a distinct and an Order by clause with DESC parameter specified [message #452956 is a reply to message #452954] Mon, 26 April 2010 10:09 Go to previous messageGo to next message
ljobson
Messages: 5
Registered: April 2010
Location: Fort Worth TX
Junior Member
case is open.

rewrite query? it is a peoplesoft query, much easier said then done... this is not the only instance of this type of query

Report message to a moderator

Re: Ora-03113 on a simple query with a distinct and an Order by clause with DESC parameter specified [message #452957 is a reply to message #452956] Mon, 26 April 2010 10:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you can't pin it down to an error that happened during the upgrade (ie a corrupt index or datablock) then Oracle Support is the only place to go.

Report message to a moderator

Re: Ora-03113 on a simple query with a distinct and an Order by clause with DESC parameter specified [message #452959 is a reply to message #452951] Mon, 26 April 2010 10:22 Go to previous messageGo to next message
ljobson
Messages: 5
Registered: April 2010
Location: Fort Worth TX
Junior Member
that is what we were afraid of. in the meantime I am going to try to drop and recreate the indexes just to see if that does have any impact.

Report message to a moderator

Re: Ora-03113 on a simple query with a distinct and an Order by clause with DESC parameter specified [message #453741 is a reply to message #452952] Fri, 30 April 2010 09:21 Go to previous messageGo to next message
ljobson
Messages: 5
Registered: April 2010
Location: Fort Worth TX
Junior Member
Oracle has finally acknowledged this as a bug their work around is to set "_disable_function_based_index"=true . This allows the query to work. Unfortuantely this query is in a PeopleSoft Database using PeopleTools 8.49 which switched a majority of their indexes to Function based index for performance reasons. In our case the solution is not possible, and since it is in PeopleSoft application rewitting a delivered query is not an option either so now it is time to sit and wait....

Report message to a moderator

Re: Ora-03113 on a simple query with a distinct and an Order by clause with DESC parameter specified [message #453742 is a reply to message #453741] Fri, 30 April 2010 09:26 Go to previous message
cookiemonster
Messages: 13971
Registered: September 2008
Location: Rainy Manchester
Senior Member
ljobson wrote on Fri, 30 April 2010 15:21
Oracle has finally acknowledged this as a bug their work around is to set "_disable_function_based_index"=true .


Ouch!

Thanks for letting us know.

Report message to a moderator

Previous Topic: ORA-01555 Snapshot Too Old
Next Topic: SQL query help for reporting: need to invert the column data into columns[ORACLE 10g] OBIEE
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Sep 04 02:18:13 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.