0

I'm Trying to connect to a remote DB using Sqlplus, but the password contains a special character (!). The SID is working fine, but Sqlplus is complaining about it when I use the ! in the password 

/usr/lib/oracle/18.3/client64/bin/sqlplus -s bloth/hods!@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=remoteServer)(Port=1521))(CONNECT_DATA=(SID=SUDFR)))

ORA-12505: TNS:listener does not currently know of SID given in connect

on the other hand, when I remove the special character I get

ORA-01017: invalid username/password; logon denied

any idea how to fix that?

Improve this question

2 Answers 2

Reset to default
1

Works with single quotes.

[oracle@o71 ~]$ sqlplus u1/u1!@(description=(address=(protocol=tcp)(port=1521)(host=o71.balazs.vm))(connect_data=(service_name=min18_o71)))
-bash: !@: event not found
[oracle@o71 ~]$ sqlplus 'u1/u1!@(description=(address=(protocol=tcp)(port=1521)(host=o71.balazs.vm))(connect_data=(service_name=min18_o71)))'

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Mar 2 09:07:54 2019
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sat Mar 02 2019 09:07:15 +01:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

SQL>
Improve this answer
1
  • Bravissimo!! It works like a Charm
    – Driven
    Mar 2, 2019 at 19:07
0

The best way to fix is change the password. There are several special characters that become problematic when used in a password, because the OS (or other intervening software) uses those characters as an indicator.

Certainly the '@' and '$' characters fall into this category.
The '@' because sqlplus itself sees that as the terminator indicateing that what follows is the net service name. So

sqlplus scott/myp@ssword@orcl

is seen as a password of 'myp', and a net service name of 'ssword'

And '$' is problematic on *nix platforms because the OS sees it as indicating to substitute the value of an environment variable. So assume you have an environment variable name word=fubar, with

sqlplus scott/mypas$word@orcl

The shell processor will change that to 'scott/mypasfubar@orcl' before calling sqlplus and passing the rest of the line.

This doesn't mean you can't use any special characters in passwords, but you have to be careful about which ones you allow.

Improve this answer
3
  • 1
    Unfortunately I don't own that DB, it's a remote DB.
    – Driven
    Mar 2, 2019 at 19:07
  • 1
    Also the confort of anyone using sqlplus should not drive the guidelines for setting passwords. In real life, applications will connect to databases. For thick clients, I imagine the user who connects will be prompted for the password, so special characters are irrelevant. For web applications (running inside some application server), the password is going to come from some config file or wallet. Here also special characters play no role.
    – Albert Godfrind
    Mar 3, 2019 at 9:42
  • @AlbertGodfrind - I don't want to get into a spitting match, but I'd have to respond with "perhaps in your perfectly imagined world." In my 30+ years in this business I've seen a lot of installations that don't match your expectations. I guess we'll just have to agree to disagree.
    – EdStevens
    Mar 3, 2019 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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