ORA-00905: missing keyword when trying to create a synonym

Question

I have a problem when I try to create a synonym like this:

CREATE SYNONYM B2H.BILLING_USER_ACTIVITY@PACOMNET 
 FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM;

Why does this happen and how can I correct it?

Answer 1

As the documentation says implicitly, you can create synonyms in the current DB only. So the first @PACOMNET in the synonym name is unnecessary (and causing the error). You should instead connect to the PACOMNET database and run

CREATE SYNONYM B2H.BILLING_USER_ACTIVITY
FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM;

Answer 2

if one enters the statement in sqlplus (Oracle10.2, Solaris 5.10) I get the following is displayed:

SQL> CREATE SYNONYM BILLING_USER_ACTIVITY@PACOMNET 
  2   FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM;
CREATE SYNONYM BILLING_USER_ACTIVITY@PACOMNET
                                    *
ERROR at line 1:
ORA-00905: missing keyword

After the statement the failing line is printed and below the failing line an asteriks (*) marks the character where the sql parser has located the problem. So the problem is located at the '@' character. The syntax diagram of the 'create synonym' statement can be found in the manual

CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM
   [ schema. ] synonym
   FOR [ schema. ] object [ @ dblink ] ;

In contrast to the object specification after the 'FOR' the synonym specification before the for does not contain a '@dblinK'.

For syntax problems you should use an appropriate tool (e.g. sqlplus) that displays the exact position of the error where the statement fails.

Answer 3

In Oracle, DDL on remote database is not permitted. One likely reason is that a distributed transaction commit can not be initiated at the remote site (you can't COMMIT@remote) and since DDL statements include a commit they are not permitted.

You would get an ORA-02021 with other DDL statements:

DDL operations are not allowed on a remote database

You can create the database link by connecting directly to the remote database.

As suggested in the askTom discussion, you can also use DBMS_JOB@remote or DBMS_SCHEDULER@remote to create a distinct remote transaction that can initiate the DDL statement.