Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
As the documentation says, you can create sysnonyms in the current DB only. So the first @PACOMNET in the sysnonym name is unnecessary (and causing the error). –  dezso Nov 12 '12 at 10:21
1  
As an aside, you should probably sort your DB link names and sqlnet config out too. –  Phil Nov 12 '12 at 11:11
add comment

3 Answers

up vote 2 down vote accepted

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;
share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.