how I can create synonym with "long refence object name"

Question

After I created synonym, reference object name change; my script:

create OR REPLACE SYNONYM [schema name].[synonym name] FOR [shema name]."long refence object name"@dblinkname;

After created I look for "long refence object name":

It's changed to "/3248857lonobjectname"

Answer 1

The table_name transformation comes from " you used in the synonym creation.

1. (You don't need the [schema name]. prior the synonym name. ) ? or is it my Oracle release that prevent doing this?

2. I would remove the " in the synonym creation, except if you did use them for the table creation in the source db.

The right syntax for this should rather be:

CREATE OR REPLACE SYNONYM [synonym name] 
  FOR [shema name].long_refence_object_name@dblinkname;

edit

Now we now a little more.

• on the MSSQL, create a synonym too (adapt the syntax):

  CREATE PUBLIC SYNONYM long_refence_object_name 
        FOR [shema name]."long refence object name"
  

• then you can use it so from Oracle

  CREATE OR REPLACE SYNONYM [synonym name] 
    FOR long_refence_object_name@dblinkname;
  

edit2:

No, in fact this is your synonym name that is too long. But you cannot have objects with more than 30 chars. See here why.

When I do this:

create or replace synonym "my" for SCHM.very_long_namevery_long_nameve@QGM1.WORLD;

My synonym works (I can access remote table), and I see it in user synonyms:

OPS$CHOJ04A my  SCHM VERY_LONG_NAMEVERY_LONG_NAMEVE QGM1.WORLD

But when I do that:

create or replace synonym "myvery_long_namevery_long_nameve" 
   for SCHM.very_long_namevery_long_nameve@QGM1.WORLD;

Then I can't access the object with

ORA-00972: identifier is too long

and I this this horrible name:

OPS$CHOJ04A /1d23ec99_MYVERY_LONG_NAMEVERY  SCHM VERY_LONG_NAMEVERY_LONG_NAMEVE QGM1.WORLD

Conclusion:

As far as I know, you can't have a synonym larger than 30 chars for any object in Oracle. This is a product limitation.

You still can use the synonym you created as follows:

SELECT * FROM "/1d23ec99_MYVERY_LONG_NAMEVERY";

Not sure it helps if you can't change the queries in your application.