Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Can I run SDO_Migrate.to_current on synonyms?

user13797179Apr 9 2014 — edited Apr 10 2014

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.

Comments

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.
[Deleted User]

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.

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.

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

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 8 2014
Added on Apr 9 2014
#database-key-features, #spatial
4 comments
1,865 views