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?
As the documentation says implicitly, you can create synonyms in the current DB only. So the first
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.
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
You would get an
You can create the database link by connecting directly to the remote database.
As suggested in the askTom discussion, you can also use