This discussion is archived
7 Replies Latest reply: Nov 22, 2012 3:18 PM by user567271 RSS

Can applications share the same Oracel Spatial db?

user567271 Newbie
Currently Being Moderated
I am a DBA however am not familiar with Oracle Spatial. I know that on Oracle db spatial info is stored in MDSYS objects. Just wonder if it is possible to have two seperare applications sharing the same Oracle SID that has Spatial option installed? Would that cause any conflicts (since the applications share the same set of MDSYS objects)? Note that the applications have nothing to do with each other and use difefrent Oracle db usernames.

Thanks
  • 1. Re: Can applications share the same Oracel Spatial db?
    Simon Greener Journeyer
    Currently Being Moderated
    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.

    regards
    Simon
  • 2. Re: Can applications share the same Oracel Spatial db?
    user567271 Newbie
    Currently Being Moderated
    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.
  • 3. Re: Can applications share the same Oracel Spatial db?
    Simon Greener Journeyer
    Currently Being Moderated
    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...
    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');
    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.

    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.

    regards
    Simon
  • 4. Re: Can applications share the same Oracel Spatial db?
    user567271 Newbie
    Currently Being Moderated
    Than you Simon for detailed answers. It is helpful.

    Regards
    Long
  • 5. Re: Can applications share the same Oracel Spatial db?
    Stefan Jager Journeyer
    Currently Being Moderated
    Simon,
    when you create a table with an sdo_geometry column a number of secondary tables are created.
    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 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.
    Ignore them at all times.
    This, however, is very very true. Do NOT mess with the MDRS_#####$ sequence and the MDRT_#####$ table.

    And on topic:
    Long,

    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.

    HTH,
    Stefan
  • 6. Re: Can applications share the same Oracel Spatial db?
    Simon Greener Journeyer
    Currently Being Moderated
    Stephan,

    Quite correct, I was thinking about the RTree tables and wrote about create table.

    regards
    Simon
  • 7. Re: Can applications share the same Oracel Spatial db?
    user567271 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points