ORA-13236 ORA-13234 ORA-29400 ORA-00942 Snapshot too old or Recursive fetch error] in Oracle

Hi,

If you migrate and upgrade Oracle database with Spatial Component, you can get ORA-29903 ORA-13236 ORA-13234 ORA-29400 ORA-00942  errors.

Oracle spatial is being used especially in geometric and map objects. If you are using map and geometric objects on your application, probably you should use Oracle spatial.

For Example, Following query is pointing specific location on map.

select * from msdeveci.map where scale = 1000 and 
SDO_CONTAINS(geoloc, SDO_GEOMETRY('POINT(420810.999158298 4450531.56836373)',2320)) = 'TRUE';

When you migrate and Upgrade Oracle database from 11g to 12c, you can get following error during execution above query.

select * from msdeveci.map where scale = 1000 and 
SDO_CONTAINS(geoloc, SDO_GEOMETRY('POINT(420810.999158298 4450531.56836373)',2320)) = 'TRUE';

ORA-29903: error in executing ODCIIndexFetch() routine
ORA-13236: internal error in R-tree processing: []
ORA-13236: internal error in R-tree processing: [Snapshot too old or Recursive fetch error]
ORA-13234: failed to access R-tree-index table [MDRT table]
ORA-29400: data cartridge error
ORA-00942: table or view does not exist

To solve this problem, You should check Oracle spatial option if it is valid or not with following article.

https://ittutorial.org/2019/03/18/oracle-12c-list-components-installed/

If Oracle spatial is invalid then make it valid with DBCA tool. Run DBCA and choose Configure database, and select Oracle spatial and Oracle text option.

When Oracle spatial is valid, problem will be solved.

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/