This content has been marked as final. Show 7 replies
Oracle spatial is a set of data types and functionality that is akin to an Oracle Date + Arithmetic.
There are no more substantive issues that would affect more than one application accessing an Oracle spatial database than would occur when multiple applications share any other database.
Thanks Simon. We have a number of Oracle SID that are shared by different appls, and for each appl I created a separate tablespace so it has its own area in the database.
With a spatial db where users share the same set of MDSYS tables, just wonder if the tables store user information along with data in a row? That is, if I do an datapump export for a schema, would the export dmp file contains rows in MDSYS tables that belong to the specified schema?
Sorry about the basic question, but I plan to read and play with Oracle spatial to understand it more.
1 person found this helpful
With a spatial db where users share the same set of MDSYS tables, just wonder if the tables store user information along with data in a row? That is, if I do an datapump export for a schema, would the export dmp file contains rows in MDSYS tables that belong to the specified schema?The only user data that is stored in the MDSYS schema is the spatial metadata that describes the data held in an sdo_geometry column in a table. This is needed for when creating an RTree spatial index.
For simplicity, Oracle Spatial is the SDO_GEOMETRY data type (and some other spatial types but you can learn about these later).
I guess if Oracle had been developed from ground up with objects in mind then mdsys would probably not exist only all types, metadata tables etc stored in sys/system.
All sdo_geometry spatial data is stored in the sdo_geometry column in the table that contains it. Just like you can have 2+ date columns in a table you can have 2+ sdo_geometry columns...
Import and Export (including data pump versions) simply export and import tables. If the table contains an sdo_geometry column then it is handled automatically (as is any sdo_geom_metadata that describes the column. So, there is nothing for you to worry about.
create table foo ( foo_id integer, create_date date, modified_date date, geom sdo_geometry); -- Now describe the data in the geom column... INSERT INTO USER_SDO_GEOM_METADATA ( Table_Name, Column_Name, Diminfo, SRID ) VALUES ( 'FOO', 'GEOM', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', 5900000, 6100000, .05), -- Range of x/longitude ordinates SDO_DIM_ELEMENT('Y', 2000000, 2200000, .05)), -- Range of y/latitude ordinates 2872); -- Spatial Reference Identifier see mdsys.cs_srs commit; -- Now we have created the table and metadata we can create a 2D spatial index on points create index foo_geom on foo(geom) indextype is mdsys.spatial_index parameters('sdo_indx_dims=2,layer_gtype=point');
Now, when you create a table with an sdo_geometry column a number of secondary tables are created. Ignore them at all times.
Sorry about the basic question, but I plan to read and play with Oracle spatial to understand it more.Best way to learn.
If anything I have said is of use, please award points.
Than you Simon for detailed answers. It is helpful.
Strictly speaking this is not true. When you create a table with one or more SDO_GEOMETRY colum(s), only that table is created.
when you create a table with an sdo_geometry column a number of secondary tables are created.
When you then add a Spatial Index, that is when a sequence (MDRS_#####$) is created and the MDRT_#####$ table is created. This table and sequence belong with the Spatial Index and are maintained by Oracle (through the Spatial Indexing mechanism), and are also dropped when the Spatial Index is dropped.
This, however, is very very true. Do NOT mess with the MDRS_#####$ sequence and the MDRT_#####$ table.
Ignore them at all times.
And on topic:
Oracle is built as a multi-user database, and as such is perfectly capable of dealing with hundreds of users all accessing the same Schema at the same time, including Spatial stuff, so there really is nothing to worry about. I have worked for organizations which use numerous tables with more than 10 million rows, each containing it's own SDO_GEOMETRY column, with more than 100 users at the same time, sometimes with different GIS-application but still all looking at and using the same data.
Quite correct, I was thinking about the RTree tables and wrote about create table.
Thanks Stephan for reassuring that a Spatial db can be shared. I have got a Spatial db used just by one application that needs be migrated to a different server, and so plan to have it shared with other applictaions on as db instance on the new server.