Question

Unable to flush the cursor for the feature class because the spatial index is too small.

  • 23 October 2019
  • 8 replies
  • 15 views
jhawks
Rank
Badge +6

Unable to flush the cursor for the feature class 'M_SDAT' because the spatial index is too small. Increase the spatial index on 'M_SDAT' and retry

 

I have an old complicated workbench. It translates all of the NOAA Electronic Navigational Charts S-57 .000 files into Oracle for internal users and external arcgis server rest services. It's been running every Saturday on a schedule for years. lately it's been throwing the above error. I'm able to get past it and run the translation by disable that particular feature class. But every week it's breaking on a different one, so I need to find a real answer.

 

FME Server 2019.0.0.1

 

Build 19246 - win64

Oracle 12.1.0.2

8 replies

virtualcitymatt
Rank
Userlevel 4
Badge +26

Have you tried 'updating' the Writer? Does the problem occur on older versions of FME of has it just started happening with FME 2019?

I see in the writers are also truncating the table, Have you tried it with also Dropping and recreating the tables?

Can you share a log file for us?

jhawks
Rank
Badge +6

Log file attached. Yes this only started after the upgrade to 2019. But it's not consistently happening. To make things more complicated...Every week the charts updated and the number charts continues to grow.

 

For that particular Feature Class I switched the write to Drop Table: yes and Truncate Table: yes and get a different error:

 

Unable to drop the table/feature class 'M_SDAT'. The error number from ArcObjects is: '-2147215875'. The error message from ArcObjects is: {Table is already locked [HARBOUR.M_SDAT]}

 

job_3526.zip

virtualcitymatt
Rank
Userlevel 4
Badge +26

Log file attached. Yes this only started after the upgrade to 2019. But it's not consistently happening. To make things more complicated...Every week the charts updated and the number charts continues to grow.

 

For that particular Feature Class I switched the write to Drop Table: yes and Truncate Table: yes and get a different error:

 

Unable to drop the table/feature class 'M_SDAT'. The error number from ArcObjects is: '-2147215875'. The error message from ArcObjects is: {Table is already locked [HARBOUR.M_SDAT]}

 

job_3526.zip

Hmm, if it used to work I would file a ticket with Support.

 

In the mean time - I would also suggest updating the writer, if you have never done it then its possible that something important could have changed.

It's an easy step, simply right click on the writer in the Navigator window and select 'update'. It should only update the writer to the current build. No change in in Feature Types or reconfiguring needed.

 

 

In the log you will see something like below.

 

 

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Update Writer Summary

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Updated 'GEODATABASE_SDE' Writer build number from 12345 to 54321

Updated Writer only. Feature types and schema were not changed.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Here's a few similar issues I've found on the ESRI site which might be related: https://support.esri.com/en/technical-article/000010090

https://support.esri.com/en/technical-article/000008860

 

 

 

nampreetatsafe
Rank
Userlevel 1
Badge +10

Log file attached. Yes this only started after the upgrade to 2019. But it's not consistently happening. To make things more complicated...Every week the charts updated and the number charts continues to grow.

 

For that particular Feature Class I switched the write to Drop Table: yes and Truncate Table: yes and get a different error:

 

Unable to drop the table/feature class 'M_SDAT'. The error number from ArcObjects is: '-2147215875'. The error message from ArcObjects is: {Table is already locked [HARBOUR.M_SDAT]}

 

job_3526.zip

It is a bit concerning when a workspace isn't working in a new version of FME. Have you had any luck upgrading your Writer as @virtualcitymatt suggested?

A couple of other things I noticed:

  1. In your last attempt (setting Drop Table = Yes and Truncate = Yes), were you able to release the locks on the table and try again?
  2. I also noticed that you mentioned using FME Server 2019.0.0.1. The workspace, however, was last saved FME Desktop 2019.1.1.0. We suggest keeping FME Desktop and FME Server version in sync within major versions i.e. 2019.x (See FME Versions and Workspace Compatibility). But, I'm assuming you are getting this error even when running your workspace on FME Desktop? Or are you just running this on FME Server?
steveatsafe
Rank
Badge +11

Hi @jhawks,

This is a strange one. I'm curious to learn what the previous version of FME Desktop and FME Server were involved before you moved to 2019? Has the Oracle database had any patches recently?

As @nampreetatsafe asked, does it run in FME Desktop OK? I also see your workspace is on a newer build 19617 than your FME Server 19246... I don't think this is a concern (especially if you haven't updated the Writer in the workspace). Do you still have an FME Server (old version) kicking around that you could prove still works?

The lock you mentioned, that seems like ArcGIS has this table open or viewing the table schema - so you'd need to resolve this looks with ArcGIS, by closing all applications connecting to this table.

@virtualcitymatt's suggestion to upgrade the writer is a good one. I don't know of anything that might have changed on the Oracle Writer - but it is possible, however, this might introduce a problem if the new engine on Desktop isn't compatible with the older FME Server engine. This is a possible outcome if you do this.

Some other things,

Can you have your DBA get involved and review the database alert log for related errors to this (or do a session trace when FME is connected and writing (sometimes hard to accomplish and a full DB trace is required)?

Is this simply a tablespace problem? Because it is random... not affecting the same feature each week it could indicate a size limitation on the tablespace or the underlying related data files. So see if the DBA can spot anything.

Something to be aware of is if something fails to write because the tablespace can't grow, the data can get flushed and when you look at the tablespace after the fact - the free space may be looking good but when you run the workspace and monitor this it may show something different. So please keep that in mind when the DBA is looking into things.

As you've shared, the features and data are growing each week.

Very curious how this one gets resolved!

SteveatSafe
steveatsafe
Rank
Badge +11

@jhawks,

There's also a small chance that this is a data issue and the spatial index update is failing for some reason when new data is inserted. Do you know if you disable the index when writing to the tables (could be something to try) then rebuild the index after the fact... if a bad geometry is encountered you'd get an error with the index creation/rebuild.

I'm wondering because this moves around - not the same feature each week, is it data that hasn't been fully validated before loading, gets cleaned up and then works the next week? Or, is it possible applications are opened and connected to the tables being updated that cause this strange error.

This is a Geodatabase (on Oracle) Writer (not Oracle as I had mistaken)... there is the sde intercept log that might show more information as well.

Steve

SteveatSafe
jhawks
Rank
Badge +6

It can't be file locks, specifically to avoid those we download the file from web rather than pull them over from the servers in the office right next to us. I'm going to file a tech support ticket, need to solve this. Also occasionally geting arcobjects errors too.

steveatsafe
Rank
Badge +11

It can't be file locks, specifically to avoid those we download the file from web rather than pull them over from the servers in the office right next to us. I'm going to file a tech support ticket, need to solve this. Also occasionally geting arcobjects errors too.

Thanks @jhawks, I see your support ticket. We will followup here when we get to the bottom of it.

SteveatSafe

Reply


Community Stats

28,688
Posts
109,431
Replies
36,542
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.