Effect of OS Terminal Setting “STTY” on Oracle Database

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 = '[email protected]' 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. 😉

Tags: databaseoraclesp2-0042Unix