Few days back, while trying to create a synonym over dblink I experienced following error:
SQL> create synonym synonymn_name for dual@DBLINK9i; SP2-0042: unknown command "DBLINK9i" - rest of line ignored
Strange error !!!
Also while trying to perform a simple select statement over another dblink:
SQL > select * from dual@dblink10g; SP2-0042: unknown command "DBLINK10g" - rest of line ignored.
It also failed with a similar error, though the select over this dblink was working fine earlier.The tnsnames.ora entry was correct and double checked.So what happened to this dblink now?
From other database I was able to perform a simple select over same dblink.
While I was researching this, I came to know that sql queries also started failing :
SQL> UPDATE HRRECORD_TEST SET EMAIL_ADDRESS = 'firstname.lastname@example.org' WHERE NAME = 'XYZ'; SP2-0734: unknown command beginning "123.com'..." - rest of line ignored. SQL>
One Notable thing was that all the queries which failed contained "@" symbol and I also got this error while create a synonym over dblink (having "@" symbol).
These two issues were related, Then I tried to create a synonym locally and it went fine. This led to conclusion that sql prompt was not recognising "@" symbol.
So it was clear that the problem was with terminal settings at OS level for oracle user.
Searcing google for terminal setting at OS, I found a command which is used to Sets options for your terminal i.e "stty". By using stty we can change the terminal settings.
Now checking the current terminal settings using:
# stty -a speed 9600 baud; line = 0; rows = 36; columns = 80 min = 4; time = 0; intr = DEL; quit = ^\; erase = DEL; kill = @ eof = ^D; eol = ^@; eol2 <undef>; swtch <undef> stop = ^S; start = ^Q; susp <undef>; dsusp <undef> werase <undef>; lnext <undef> parenb -parodd cs7 -cstopb hupcl -cread -clocal -loblk -crts
From this output I found that there were two settings causing problem:
1) kill = @
2) eol = ^@
Then, to overcome this I added the following two lines in the oracle user .profile
stty kill ^U
stty eol ^E
Above change will force oracle user to use ^U to kill any session and ^E to end a line instead of using @ for these purpose.
After making these changes in oracle user's .profile everything went fine. In the end it turned out OS issue whose settings was modified by sysadmin. 😉