Re: ORA-3113 During delete from with subquery


Scott Stefick wrote:

>Anyone else experience this?
>OS: Solaris 8
>DB: 10g (10.1.0.3)
>My delete returns an ORA-3113 when I execute this on my 10g databases,
>but it works fine on my 8.1.7 databases.
>
>Here is my DML:
>
>DELETE FROM v_trn_event_attendees=20
>WHERE pers_id =3D 86409
>AND class_id IN (SELECT a.class_id=20
>                        FROM trn_events e, trn_event_attendees a =20
>                        WHERE e.class_id =3D a.class_id=20
>                        AND pers_id =3D 86409
>                        AND element_id =3D 1015891
>                        AND date_conducted =3D (select max(date_conducted)=
>=20
>                                                            from
>trn_events e, trn_event_attendees a
>                                                            where
>e.class_id =3D a.class_id
>                                                            and pers_id =3D=
>86409
>                                                            and
>element_id =3D 1015891
>                                                            and
>date_conducted <=3D '25-APR-2005'))
>/
>
>
>Thanks in advance..
>Scott
>--
>  
>
Scott,

    A 3113 error is usually associated with a .trc file in the 
user_dump_dest directory. It may give a clue, but it smells like a bug.
Rewriting queries also sometimes magically works around bugs.

Try this (I hope the values are hard-coded for testing purposes only. 
Also, avoid relying on implicit date formats)

DELETE FROM v_trn_event_attendees
WHERE (pers_id, class_id) IN
    (SELECT pers_id, a.class_id
     FROM trn_events e,
          trn_event_attendees a
     WHERE e.class_id = a.class_id
       AND (pers_id, element_id, date_conducted) = 
                (select pers_id, element_id, max(date_conducted)
                 from trn_events e,
                      trn_event_attendees a
                 where e.class_id = a.class_id
                   and pers_id = 86409
                   and element_id = 1015891
                   and date_conducted <= to_date('25-APR-2005', 'DD-MON-YYYY')
                 group by pers_id, element_id))
/


If you have the composite indexes I think you have, it may work well. 
You can probably do even better with analytic functions.

HTH,

SF

--
http://www.freelists.org/webpage/oracle-l

Other related posts: