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.
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];
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.
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 🙂