Build Geometric Network failing in Oracle at ArcGIS 10

[DavidKlein]

Hi All,

I've loaded a heap of electric data into an Oracle geodatabase.  When I try to build a network from this data, it fails with the following error:

Build Geometric Network failed.
DBMS table not found [ ORA-00942: table or view does not exist] [GISMIG1.T_1_PolyErrors] [STATE_ID = 7]
DBMS table not found [ ORA-00942: table or view does not exist] [GISMIG1.T_1_LineErrors] [STATE_ID = 7]
DBMS table not found [ ORA-00942: table or view does not exist] [GISMIG1.T_1_PointErrors] [STATE_ID = 7]
DBMS table not found [ ORA-00942: table or view does not exist] [GISMIG1.T_1_DirtyAreas] [STATE_ID = 7]

2 other relevant facts I have found:

1) I can load this same data into a File Geodatabase and the Geometric Network builds just fine.

2) These 4 T_* tables that it is looking for do in fact briefly exist, shortly after I begin the Geometric Network build process.  Then they are gone again, and the build process blows up complaining it cannot find them.  It is as if ArcGIS is creating these tables for temporary use, but deleting them before it is done with them.

Has anyone seen this behavior?  Any advice how to solve it? 

Cheers,
Dave

[EdwardBlair]

Thanks much for the reply.!

I'm running Oracle 11.2.0.1.0.

Result of SHOW PARAMETER UNDO are

undo_management   AUTO
undo_retention         900
undo_tablespace       UNDOTBS1

Ed

[WilliamCraft]

What are the results of this query when run against Oracle as SYS?

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec)))"OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'; 

[EdwardBlair]

ACTUAL UNDO SIZE:               8880
UNDO RETENTION:                    900
OPTIMAL UNDO RETENTION:  101895

I'm not an Oracle wiz - but it would seems my undo retention is much less than optimal.

Ed

[WilliamCraft]

Maybe.  Oracle is supposed to automatically tune the undo retention size when set to AUTO.  If you're open to modifying the value and this isn't a production database, try something like this in SQLplus and then attempt to create your geometric network again after making a new connection:

ALTER SYSTEM SET UNDO_RETENTION = 150000;

If it doesn't work, then change it back to the default setting of 900:

ALTER SYSTEM SET UNDO_RETENTION = 900;

[EdwardBlair]

This is not a production instance... I'll give it a try and keep you posted.

Thanks again!
Ed

[EdwardBlair]

crafty (Hope its ok to use that name) -

I would love to buy you a beer if I ever get the chance.   The network build completed with the results I was expecting/hoping for.

Thanks a million!
Ed

[WilliamCraft]

Ed, I'm so glad it worked for you!  Thanks for the follow-up and for letting me know.  I'm up for a beer anytime... no need to buy.  Maybe sometime if you're at the Esri UC we can get a chance to meet.  Have a great weekend!

[EdwardBlair]

Now, unfortunately, this issue has re-surfaced.  I've had to drop and re-create the network on this database and I'm getting the original error -- even with the undo_retention parameter set to 150000.  I re-executed the SQL and found that the optimal value was now at about 278000 so I reset undo_retention to 300000.  Same error results.

Now I'm going to try two things:

1.  Return the parameter to its original setting - 900
2.  Contact ESRI tech support

Ed

[EdwardBlair]

OK, in this case I found I had inadvertently assigned a subtype to the AncillaryRole field on a point feature class.  There should have been no subtype.  Also, all values in this field were blank.  Removing the subtype assignment allowed the network build to complete successfully.

There used to be an error message when null subtype values were found.

Anyway, this was my problem.

Ed