My Environment: ArcSDE 10.1, Linux RHEL 6, Oracle 11g2
In our environment we have a two corporate databases Database1(non-spatial) and Database 2(geodatabase). Database 1(non-spatial) has a table TAB1 with geometry column and it gets continiously updated. I want to create a spatially enabled view in DATABASE2(geodatabase) of this table to get updates simultaneously to our GIS users using Database2(geodatabase).
Please help me with the best way to do this.
My Homework: In DATABASE2 .. I created oracle view .. registered it as layer, but it failed to draw geography in ArcMap with message"Spatial Index needed". It once again failed when i tried to create spatial index on the view.
You can try with materialized views both with ESRI st_geometry or Oracle sdo_geometry. I think you can build a spatial index over a materialized view.
Let me know.
Jesus de Diego
I have a setup to synchronize data between two tables with help of materialized views. Its working perfectly. What i was trying to do is to look for a different method. Your suggestion is one of the successful setups for synchronization.
Thanks once again.
You can create a new table/layer on DATABASE2 and create triggers for updating this table/layer each time table in DATABASE1 is modified...
Jesus de Diego Alarcon
In all RDBMSes except Oracle (11g and below), different "databases" are actually stored in the same instance. The fact that this is not true in Oracle means you can't use the same techniques as recommended for other RDBMS software.
It is not possible to create an index on a view. Views always use the index on the base table, unless the view is really an Oracle database link, in which case it often won't use the index even then.
What you need to do is make a decision. You can:
Since I'm a data manipulation geek, I'm usually asked to do option 2, but it's often more complex and subject to periodic re-synchronization than most project managers realize.
Query layer process: I can view the table in database1 through ArcCatalog/ArcMap 10.3 as spatial table 'iconed', but when i switch to 'Geography' in ArcCatalog or When i drag it to ArcMap the fetures are not drawn, only table content is view able. What is the issue i don't know(Please suggest)
Replication: Recently i implemented this process, writing a package(oracle procedure) and scheduled it to run on specific intervals(job scheduler) which syncs data.