4 Replies Latest reply: Apr 10, 2014 8:23 AM by B Hall RSS

    Can I run SDO_Migrate.to_current on synonyms?

    user13797179

      When running the SDO_Migrate on synonyms, I am getting the following error. The synonym exists for table and an entry exists in user_sdo_geom_metadata for the table.

        Error occurs when executing the SQL string:

        "execute sdo_migrate.to_current('<tablename>')"

        ORA-13261: geometry table <tablename> does not exist

      I found that the original schema (say A) which contained this table can run the SDO_Migrate.to_current, however, when I created a public synonym and tried to run the sdo migrate from the new schema (Say B), I am getting this error. All grants have been given to the new Schema (B). Also the synonym name and the tablename are the same.

      The new schema (B) does not have any tables, nor does it have privileges to create new tables. I cannot change the schema behavior, however I need a way to migrate the table from the new schema (B).

      Any required grants on the table can be given to new Schema, however the new schema cannot create or alter tables.

        • 1. Re: Can I run SDO_Migrate.to_current on synonyms?
          Emad Al-Mousa
          Error:ORA-13261
          Text:geometry table TABLE_NAME does not exist

           

          Cause:The specified geometry table does not exist in the current schema.
          Action:Create a table containing a column of type SDO_GEOMETRY and a column
          of type NUMBER for the GID values.
          • 2. Re: Can I run SDO_Migrate.to_current on synonyms?
            Stefan Jager

            As far as I'm aware Spatial has never supported synonyms (public or otherwise), at least not in the Object model. You should be able to use the <schemaname>.<tablename> notation however.

            Best thing to do is to temporarily give schema B the necesary privileges. After all, how often are you going to migrate?

            Oh, and if schema B needs to be able to create spatial indexes, it needs to have create sequence and create table privileges.

            • 3. Re: Can I run SDO_Migrate.to_current on synonyms?
              user13797179

              Hi Stefan,

              Thanks for the answer, but the solution did not work for me.

              I tried the <schemaname>.<tablename> notation, but that did not work. I tried SDO_Migrate.to_current('<schemaname>.<tablename>'), SDO_Migrate.to_current(<schemaname>.<tablename>), etc., with quotes, without quotes and other combinations, but none worked.

               

              I am not sure what privileges are required in order to make the migrate work. I cannot however give create table and create indexes privileges to schema B.

              • 4. Re: Can I run SDO_Migrate.to_current on synonyms?
                B Hall

                The simple answer is - and as you found, you cannot specify the schema name in the tablename input, you have to be logged in (or possibly by using a proxy) as the schema owner. This is a big shortcoming of many spatial functions, as they cannot be run as sys in a script against multiple schemas. Documentation (as of 11.2) says just tablename:

                 

                 

                SDO_MIGRATE.TO_CURRENT(

                     tabname IN VARCHAR2

                     [, column_name IN VARCHAR2]);

                 

                 

                BTW, I am confused at what you are trying to do, and what rights "B" has. Are you trying to say that you want to do this (for whatever reason) as "B" although the data is in "A"?

                 

                Bryan