Error while COMMIT after INSERT

[Viji Lakshmi]

Hi,

 

I am getting error while doing a commit after INSERT.

 

Table is having spatial data and the database is oracle 10.2.0.2.0 version.Please find the error as below.

 

ORA-00604: error occurred at recursive SQL level 1
ORA-13236: internal error in R-tree processing: [reading node (mdrbin_mem_ins_rt)]
ORA-29400: data cartridge error
ORA-00942: table or view does not exist
ORA-06512: at "MDSYS.SDO_IDX", line 142
ORA-06512: at line 1.

 

Can any one please provide your inputs for the same?

 

Thanks,

Vijayalakshmi

[sampath]

Hi Vijayalakshmi

Please find below the extract of Doc ID :443422.1 from metalink


Applies to:
Oracle Spatial - Version: 10.1.0.4.0 to 10.2.0.3.0
This problem can occur on any platform.

Symptoms
A job runs periodically that in turn runs a procedure to truncate and
insert rows into a table that has an associated spatial index. The
data appears to populate the table but when a commit is performed the
following error occurred:

ORA-00604: error occurred at recursive SQL level 1

ORA-13236: internal error in R-tree processing: [insertion at root
(mdrbin_mem_ins_rt)]
ORA-13236: internal error in R-tree processing: [integrating nonleaf
bucket (mdrbin_optmz_mem_ins_node)]
ORA-13236: internal error in R-tree processing: [wrong entry level
(mdrbin_intgrt_nonleaf_bckt)]

ORA-06512: at "MDSYS.SDO_IDX", line 142
ORA-06512: at line 1

Cause
The cause of this problem has been identified and verified in an
unpublished Bug 4570769.
This is only seen under heavy DML conditions.

As of the 10g release Spatial does not update the index until after
the commit operation completes the transaction, and it updates the
index in batches of operations determined by the SDO_DML_BATCH_SIZE
value. This is implemented to increase DML performance.
It also explains why the errors are only seen at commit time.

For more details see the Spatial User's Guide and Reference release
10.2
http://www.oracle.com/pls/db102/portal.portal_db?selected=7





Workaround
Use the following workaround until Bug 4729792 is fixed. Set
SDO_DML_BATCH_SIZE = 1 for indexes having this problem.

SQL> connect mdsys
SQL> update SDO_INDEX_METADATA_TABLE
set SDO_DML_BATCH_SIZE = 1
where sdo_index_owner = <INDEX/SCHEMA Owner>
and sdo_index_name = 'INDEX_NAME';
SQL> commit;




I think this resolves your problem.

Sampath Kumar Bodiga