Transfer ST_GEOMETRY data across Oracle Database Link - possible?

[NicholasO_Connor]

We have a Production instance Oracle 11.1.0.6.0 / SDE 9.3.1 and an Edit instance Oracle 11.1.0.6.0 / SDE 9.3.1 running on Solaris 10. ST_GEOMETRY set as default. No versioning. We have created a database link between the two instances.


I'm thinking about this method to make nightly updates from Edit to Production.

i.e.

delete * from geo.buildings (in Production)
insert into geo.buildings (in Production) select * from geo.buildings@edit

a) I realise that I would have to generate the OBJECTID in geo.buildings (in Production) via the sequence number of geo.buildings (in Production).

b) I would not use * in my final version but list the column names.

For a simple Polygon Feature Class located in the Edit instance is it possible to transfer data to Production instance across a database link? Any issues to be aware?

[NicholasO_Connor]

...Any one using Database links to transfer data?

[PaulDziemiela]

Hi Nicholas,

No one is jumping in to answer your question.  I've never tried this myself but I think the answer is "Probably No". 

You need to first check to see if both your instances involved in the database link have the same type oids for all the types and subcomponents of SDE.ST_GEOMETRY.  If you installed ArcSDE on your Oracle instance before 9.3 the values for the type oids were arbitrary and subsequent upgrades would not change them.  See
http://resources.arcgis.com/content/kbase?fa=articleShow&d=34928
So none of my production servers have the same OIDs as they date back to 9.1 days.  Attempting to move SDE.ST_GEOMETRY from one to the other is futile as the servers will not recognize the other server's types as even being SDE.ST_GEOMETRY.  It might work if both servers were fresh installs of ArcSDE.  I looked at my 11gR2 test machine on which I installed 9.3.1 fresh and those types match the ids in the above mentioned article.  My production machines are slated for an 11gR2 upgrade but even then I think the type OIDs will stay the same.  Until someone rebuilds my production servers from scratch, I am stuck.

Secondly your ST_SRID coordinate system values from SDE.ST_SPATIAL_REFERENCES must match between servers.  Otherwise one server will have no idea what coordinate system the geometry from the other server has.  We've talked about this issue in the forum and how difficult this would be conquer. See
http://forums.arcgis.com/threads/3464-How-to-ensure-consistent-ST_Geometry-SRIDs-when-importing-feat...
So the geometry on server A might have ST_SRID 66 meaning NAD83 but server B probably will have ST_SRID 66 assigned to some other coordinate system.  How do you bring this in harmony?  I have no idea.

Thirdly, what are you really after in terms of using this database link?  If all you want to do is pull geometries from one server to another, then you might get things to work with some (a lot?) effort.  But if you expect to "work" across the database link with queries and such, you can forget it.  Domain indexes such as the SDE.ST_SPATIAL_INDEX don't work across database links.  This is the same for Oracle SDO_GEOMETRY.  You might want to look at
http://forums.oracle.com/forums/thread.jspa?threadID=375036

Anyhow, that's my two cents and feel free to correct anything I misstated.  To my mind its just not worth the effort to try this.  If you are using Oracle and ArcSDE, why not overcome the first two issues by using MDSYS.SDO_GEOMETRY storage rather than SDE.ST_GEOMETRY? 

Cheers,

Paul

[BruceHarold]

Nicholas

The Data Interoperability extension solves this sort of problem.  You can get an evaluation license if you want to learn how.

Regards