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

All we need is an easy explanation of the problem, so here it is.

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

CREATE SYNONYM [email protected] 
 FOR [email protected];

Why does this happen and how can I correct it?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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 [email protected];

Method 2

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 [email protected]) 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 [email protected] or [email protected] to create a distinct remote transaction that can initiate the DDL statement.

Method 3

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

SQL> CREATE SYNONYM [email protected] 
  2   FOR [email protected];
CREATE SYNONYM [email protected]
                                    *
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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply