English

Problem: Oracle ORA-00600 internal error encountered with ArcGIS queries

Description

Random Oracle ORA-00600 errors can occur when working with ArcGIS and Oracle 10g if a table or index has a degree of parallelism greater than one.

If ArcGIS is encountering an Oracle ORA-00600 error, the first step is to inspect the Oracle alert log for the error. The Oracle alert log contains the error number and the path to the file name that contains additional information from the session that encountered the error. The following text was extracted from an Oracle alert log that contains the Oracle ORA-00600 error and the file from the session with additional information.

Code:
Mon Aug 25 09:23:35 2008
Errors in file /app/oracle/admin/geodb/bdump/geodb_p000_17415.trc:
ORA-00600: internal error code, arguments: [kxspoac : EXL 1], [23], [23], [], [], [], [], []

When inspecting the file from the user's session dump, it contains the following text that identifies the failing SQL statement.

Code:
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kxspoac : EXL 1], [23], [23], [], [], [], [], []
Current SQL statement for this session:
SELECT COUNT(OID) FROM (SELECT b.OID,b.PAGENUMBER FROM
joe_l.N_2_EStatus b WHERE b.OID NOT IN (SELECT /*+ HASH_AJ */
SDE_DELETES_ROW_ID FROM joe_l.D309 WHERE DELETED_AT IN (SELECT l.lineage_id FROM
SDE.state_lineages l WHERE l.lineage_name = :source_lineage_name
AND l.lineage_id <= :source_state_id) AND SDE_STATE_ID = 0) UNION ALL SELECT a.OID,a.PAGENUMBER FROM joe_l.A309
a,SDE.state_lineages SL WHERE (a.OID, a.SDE_STATE_ID) NOT IN (SELECT /*+ HASH_AJ */
SDE_DELETES_ROW_ID,SDE_STATE_ID FROM joe_l.D309 WHERE DELETED_AT IN (SELECT
l.lineage_id FROM SDE.state_lineages l WHERE l.lineage_name
= :source_lineage_name AND l.lineage_id <= :source_state_id) AND SDE_STATE_ID >
0) AND a.SDE_STATE_ID = SL.lineage_id AND SL.lineage_name = :source_lineage_name AND SL.lineage_id
<= :source_state_id ) V__309
----- Call Stack Trace -----

Cause

The cause of the problem is Oracle Bug 5045992 - OERI[kxspoac : EXL 1] from PQ with a numeric bind.

Note:
Additional information on this issue can be found by using Oracle's MetaLink and searching for article 458111.1.

Several users have reported this issue to ESRI and the problem can be attributed to setting the Oracle initilization parameter cursor_sharing = force or similar and having tables or indexes with the degree of parallelism greater than one.

Solution or Workaround

Alter all tables and indexes in a schema and set the value for parallel equal to one.

  1. Using SQL*Plus connect as the data owner, execute the following two procedures to alter the table and indexes degree of parallelism to one. The first procedure alters any tables that have a degree greater than one and the second procedure applies to indexes.

    Code:
    SQL> DECLARE
    2 CURSOR mycur IS SELECT table_name FROM user_tables
    3 WHERE degree NOT IN ('0','1');
    4 BEGIN
    5 FOR mylp IN mycur LOOP
    6 EXECUTE IMMEDIATE 'ALTER TABLE '||mylp.table_name||' PARALLEL 1';
    7 END LOOP;
    8 END;
    9 /

    PL/SQL procedure successfully completed.

    SQL> DECLARE
    2 CURSOR mycur IS SELECT index_name FROM user_indexes
    3 WHERE degree NOT IN ('0','1');
    4 BEGIN
    5 FOR mylp IN mycur LOOP
    6 EXECUTE IMMEDIATE 'ALTER INDEX '||mylp.index_name||' PARALLEL 1';
    7 END LOOP;
    8 END;
    9 /

    PL/SQL procedure successfully completed.

Related Information