Ora-600 When Select From Table With Spatial Data

Last updated on OCTOBER 28, 2016

Applies to:

Symptoms

On : 12.1.0.2 version, Internals Errors (ORA-600 & ORA-7445)

When attempting to

select * from ( SELECT /*+ index(o1 SEARCHES_PK) */ o1.* FROM SEARCHES o1 WHERE o1.SEARCH_ID IN ( SELECT o.SEARCH_ID FROM SEARCHES PARTITION (P_ACTIVE) o WHERE 1 != 1 UNION SELECT /*+ no_index(o SEARCHES_START_DATE_STATE_I )*/ o.SEARCH_ID FROM SEARCHES PARTITION (P_ACTIVE) o JOIN USERS owner ON owner.USER_ID = o.USER_ID LEFT JOIN SEARCHES_VEHICLE_TYPES svt ON svt.SEARCH_ID = o.SEARCH_ID WHERE 1 = 1 AND o.DTYPE = 'V' AND ( o.START_DATE <= :1 AND (o.END_DATE IS NULL OR o.END_DATE >= :2 ) ) AND NOT EXISTS ( SELECT l.TARGET_COMPANY_ID FROM COMPANY_LIKINGS l WHERE l.COMPANY_BID = :3 AND l.LIKING = 'BLACKLISTED' AND l.TARGET_COMPANY_ID = owner.COMPANY_ID ) AND (SDO_RELATE(o.FROM_SDO, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(:4 , :5 , NULL), NULL, NULL), 'mask=ANYINTERACT') = 'TRUE') AND (SDO_RELATE(o.TO_SDO, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(:6 , :7 , NULL), NULL, NULL), 'mask=ANYINTERACT') = 'TRUE') AND o.HAZARDOUS = :8 AND (o.GOODS_TYPE = :9 OR o.GOODS_TYPE = 'ALL') AND ((:10 BETWEEN o.LENGTH_MIN AND o.LENGTH_MAX) OR (:11 >= o.LENGTH_MIN AND o.LENGTH_MAX IS NULL) OR (:12 <= o.LENGTH_MAX AND o.LENGTH_MIN IS NULL) OR (o.LENGTH_MAX IS NULL AND o.LENGTH_MIN IS NULL)) AND ((:13 BETWEEN o.WEIGHT_MIN AND o.WEIGHT_MAX) OR (:14 >= o.WEIGHT_MIN AND o.WEIGHT_MAX IS NULL) OR (:15 <= o.WEIGHT_MAX AND o.WEIGHT_MIN IS NULL) OR (o.WEIGHT_MAX IS NULL AND o.WEIGHT_MIN IS NULL)) AND (svt.VT_VALUE IN (:16 ) OR svt.VT_VALUE IS NULL) AND o.IS_TEST = 0 UNION SELECT /*+ ordered no_index(o SEARCHES_START_DATE_STATE_I ) use_nl(o sar) use_hash(sar arrReg) */ o.SEARCH_ID FROM SEARCHES PARTITION (P_ACTIVE) o JOIN USERS owner ON owner.USER_ID = o.USER_ID JOIN SEARCHES_ARRIVALS_REGIONS sar ON sar.SEARCH_ID = o.SEARCH_ID JOIN LOCATIONS arrReg ON sar.LOCATION_ID = arrReg.LOCATION_ID LEFT JOIN SEARCHES_VEHICLE_TYPES svt ON svt.SEARCH_ID = o.SEARCH_ID WHERE 1 = 1 AND o.DTYPE = 'V' AND ( o.START_DATE <= :17 AND (o.END_DATE IS NULL OR o.END_DATE >= :18 ) ) AND NOT EXISTS ( SELECT l.TARGET_COMPANY_ID FROM COMPANY_LIKINGS l WHERE l.COMPANY_BID = :19 AND l.LIKING = 'BLACKLISTED' AND l.TARGET_COMPANY_ID = owner.COMPANY_ID ) AND (SDO_RELATE(o.FROM_SDO, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(:20 , :21 , NULL), NULL, NULL), 'mask=ANYINTERACT') = 'TRUE') AND sdo_relate(arrReg.regional_sdo, :22 , 'mask=ANYINTERACT') = 'TRUE' AND arrReg.LOCATION_TYPE != 'SETTLEMENT' AND o.HAZARDOUS = :23 AND (o.GOODS_TYPE = :24 OR o.GOODS_TYPE = 'ALL') AND ((:25 BETWEEN o.LENGTH_MIN AND o.LENGTH_MAX) OR (:26 >= o.LENGTH_MIN AND o.LENGTH_MAX IS NULL) OR (:27 <= o.LENGTH_MAX AND o.LENGTH_MIN IS NULL) OR (o.LENGTH_MAX IS NULL AND o.LENGTH_MIN IS NULL)) AND ((:28 BETWEEN o.WEIGHT_MIN AND o.WEIGHT_MAX) OR (:29 >= o.WEIGHT_MIN AND o.WEIGHT_MAX IS NULL) OR (:30 <= o.WEIGHT_MAX AND o.WEIGHT_MIN IS NULL) OR (o.WEIGHT_MAX IS NULL AND o.WEIGHT_MIN IS NULL)) AND (svt.VT_VALUE IN (:31 ) OR svt.VT_VALUE IS NULL) AND o.IS_TEST = 0 UNION SELECT /*+ ordered no_index(o SEARCHES_START_DATE_STATE_I ) use_nl(o sdr) use_hash(sdr depReg) */ o.SEARCH_ID FROM SEARCHES PARTITION (P_ACTIVE) o JOIN USERS owner ON owner.USER_ID = o.USER_ID JOIN SEARCHES_DEPARTURES_REGIONS sdr ON sdr.SEARCH_ID = o.SEARCH_ID JOIN LOCATIONS depReg ON sdr.LOCATION_ID = depReg.LOCATION_ID LEFT JOIN SEARCHES_VEHICLE_TYPES svt ON svt.SEARCH_ID = o.SEARCH_ID WHERE 1 = 1 AND o.DTYPE = 'V' AND ( o.START_DATE <= :32 AND (o.END_DATE IS NULL OR o.END_DATE >= :33 ) ) AND NOT EXISTS ( SELECT l.TARGET_COMPANY_ID FROM COMPANY_LIKINGS l WHERE l.COMPANY_BID = :34 AND l.LIKING = 'BLACKLISTED' AND l.TARGET_COMPANY_ID = owner.COMPANY_ID ) AND sdo_relate(depReg.regional_sdo, :35 , 'mask=ANYINTERACT') = 'TRUE' AND depReg.LOCATION_TYPE != 'SETTLEMENT' AND (SDO_RELATE(o.TO_SDO, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(:36 , :37 , NULL), NULL, NULL), 'mask=ANYINTERACT') = 'TRUE') AND o.HAZARDOUS = :38 AND (o.GOODS_TYPE = :39 OR o.GOODS_TYPE = 'ALL') AND ((:40 BETWEEN o.LENGTH_MIN AND o.LENGTH_MAX) OR (:41 >= o.LENGTH_MIN AND o.LENGTH_MAX IS NULL) OR (:42 <= o.LENGTH_MAX AND o.LENGTH_MIN IS NULL) OR (o.LENGTH_MAX IS NULL AND o.LENGTH_MIN IS NULL)) AND ((:43 BETWEEN o.WEIGHT_MIN AND o.WEIGHT_MAX) OR (:44 >= o.WEIGHT_MIN AND o.WEIGHT_MAX IS NULL) OR (:45 <= o.WEIGHT_MAX AND o.WEIGHT_MIN IS NULL) OR (o.WEIGHT_MAX IS NULL AND o.WEIGHT_MIN IS NULL)) AND (svt.VT_VALUE IN (:46 ) OR svt.VT_VALUE IS NULL) AND o.IS_TEST = 0 UNION SELECT /*+ ordered use_nl(o sdr) INDEX(sdr DEP_REGION_SEARCH_FK_I) use_hash(sdr depReg) use_nl(depReg sar) INDEX(sar ARR_REGION_SEARCH_FK_I) use_hash(sar arrReg) */ o.SEARCH_ID FROM SEARCHES PARTITION (P_ACTIVE) o JOIN USERS owner ON owner.USER_ID = o.USER_ID JOIN SEARCHES_DEPARTURES_REGIONS sdr ON sdr.SEARCH_ID = o.SEARCH_ID JOIN LOCATIONS depReg ON sdr.LOCATION_ID = depReg.LOCATION_ID JOIN SEARCHES_ARRIVALS_REGIONS sar ON sar.SEARCH_ID = o.SEARCH_ID JOIN LOCATIONS arrReg ON sar.LOCATION_ID = arrReg.LOCATION_ID LEFT JOIN SEARCHES_VEHICLE_TYPES svt ON svt.SEARCH_ID = o.SEARCH_ID WHERE 1 = 1 AND o.DTYPE = 'V' AND ( o.START_DATE <= :47 AND (o.END_DATE IS NULL OR o.END_DATE >= :48 ) ) AND NOT EXISTS ( SELECT l.TARGET_COMPANY_ID FROM COMPANY_LIKINGS l WHERE l.COMPANY_BID = :49 AND l.LIKING = 'BLACKLISTED' AND l.TARGET_COMPANY_ID = owner.COMPANY_ID ) AND sdo_relate(depReg.regional_sdo, :50 , 'mask=ANYINTERACT') = 'TRUE' AND depReg.LOCATION_TYPE != 'SETTLEMENT' AND sdo_relate(arrReg.regional_sdo, :51 , 'mask=ANYINTERACT') = 'TRUE' AND arrReg.LOCATION_TYPE != 'SETTLEMENT' AND o.HAZARDOUS = :52 AND (o.GOODS_TYPE = :53 OR o.GOODS_TYPE = 'ALL') AND ((:54 BETWEEN o.LENGTH_MIN AND o.LENGTH_MAX) OR (:55 >= o.LENGTH_MIN AND o.LENGTH_MAX IS NULL) OR (:56 <= o.LENGTH_MAX AND o.LENGTH_MIN IS NULL) OR (o.LENGTH_MAX IS NULL AND o.LENGTH_MIN IS NULL)) AND ((:57 BETWEEN o.WEIGHT_MIN AND o.WEIGHT_MAX) OR (:58 >= o.WEIGHT_MIN AND o.WEIGHT_MAX IS NULL) OR (:59 <= o.WEIGHT_MAX AND o.WEIGHT_MIN IS NULL) OR (o.WEIGHT_MAX IS NULL AND o.WEIGHT_MIN IS NULL)) AND (svt.VT_VALUE IN (:60 ) OR svt.VT_VALUE IS NULL) AND o.IS_TEST = 0) ORDER BY o1.UPDATED_DATE DESC ) where rownum <= :61


the following error occurs.

ERROR
-----------------------
ORA-00600: internal error code, arguments: [20084], [2404147], [0], [2404147], [0], [], [], [], [], [], [], []

Cause