2 Replies Latest reply: Nov 29, 2008 5:13 PM by 79922 RSS

    Spatial Index and XA transaction

    vr*446726*ik
      Hi all,
      I have problem with spatial index in XA transaction.

      java.sql.SQLException: ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
      ORA-29400: data cartridge error
      ORA-14450: attempt to access a transactional temp table already in use
      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 623
      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 227

      My configuration Java 5, Tomcat 5.5, UserTransaction manager Bitronix.
      The problem disappears after dropping spatial index.
      sql statement:
      INSERT INTO ICING_SPATIAL.MAP_ISSUES ( FEATURE_ID, GEOMETRY, AUTHOR_ID, ISSUE_ID, ISSUE_STATUS, LANGUAGE, SOURCE, TEXT) VALUES ( ? ,SDO_MIGRATE.TO_CURRENT( ? , ( SELECT DIMINFO FROM ALL_SDO_GEOM_METADATA WHERE OWNER = ? AND TABLE_NAME = ? AND COLUMN_NAME = ? ) ), ? , ? , ? , ? , ? , ? )

      Full stacktrace is:
      java.sql.SQLException: ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
      ORA-29400: data cartridge error
      ORA-14450: attempt to access a transactional temp table already in use
      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 623
      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 227

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:212)
      at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:951)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
      at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:4245)

      All user transactions are commited or rollbacked because the DBA_2PC_PENDING is empty: SQL> select * from SYS.DBA_2PC_PENDING;

      no rows selected

      SQL>

      This problem occures regulary after any 2PC transaction has been rolledback. The next request causes this exception. Sometimes it appears after commit too, but I am able to reproduce it within ten or twenty requests.

      Has anybody had simillar problem?
      Thanks for any suggestions how to check what is going wrong.

      Regards,
      Zdenek Vrablik