I mostly work with SQL Server, and thought I'd like to try out Oracle a bit, in case I ever need to work with a client that uses it. I downloaded and installed Enterprise Edition on my desktop. Created a listener. So far so good.

Now it's time to create The Database. I run DBCA, use the General Purpose template with a few tweaks, get to the end, and tell it to start creating.

ORA-12154: TNS:could not resolve the connect identifier specified

Sweet. Abort. I searched around a little bit, and figured I needed a tsnames.ora. I set that up, and tried connecting with sqlplus (which I expected to fail, since no database has been created, but it ought to at least find the listener and talk to it). And sure enough, that part seemed to work. Ran DBCA again. Same error.

So a little more Google work leads me to this thread:

http://dbaforums.org/oracle/index.php?showtopic=1333

Here's the interesting line:

but I think my original attempts were failing b/c I was specifying passwords (for the SYS, SYSTEM. SYSMAN, etc.) that contained special characters that the dbca somehow didn't handle and/or pass long properly. In my case, the special character seemed to be "@".

Wait a minute. I'm using p@ssw0rd for the passwords. Could it possibly be? I ran DBCA again, using Passw0rd instead (it's a test machine, who gives a shit). This time:

Database creation complete. For details check the logfiles at:
C:\Oracle\cfgtoollogs\dbca\ORCL.

What the pissing fuck? You can't use @ in a password? Please tell me this is just a DBCA bug.

It's not that you can't use "@" signs, it's that you can't use "@"s with tools that connect via "ezconnect", which I'm guessing was happening here. Ezconnect manages its DB connection string like so: username/password@[//]host[:port][/service_name] See the problem with "@" signs in the password? There are other connection modes that don't have this issue. Yes, it's completely insane.

Duly noted, though I can't imagine there's no way to quote/escape characters in the connection string.

db2:

Duly noted, though I can't imagine there's no way to quote/escape characters in the connection string.

 

sqlplus 'username'/'password'@host-string

If your username and/or pass don't contain "illegal" characters, you can leave out the quotes. Most people omit them because their username and pass don't contain slash or "@".

Alternatively, try (assuming Windows):

SET LOCAL=myDBname

sqlplus username/'P@ssword'

But yeah, it can be annoying when you realise some characters are misinterpreted.

db2:

I searched around a little bit, and figured I needed a tsnames.ora.

 

Actually, you don't. It's only needed for networking (and even then it can be bypassed - think of it as a HOSTS file).

An alternative is:

SET ORACLE_SID=myinstancename

sqlplus / as sysdba

.. that should let you "rootwalk" directly in, no tnsnames required.

That is just common sense. Nobody uses "@" in passwords with Oracle.

Why not? I use it in other passwords.

Cassidy:

Why not? I use it in other passwords.

Oracle 7.3 manual tells you not to do it.

Nagesh:

Oracle 7.3 manual tells you not to do it.

 

Do later manuals mention it? Or are you still using 7.3?

Cassidy:

Nagesh:

Oracle 7.3 manual tells you not to do it.

 

Do later manuals mention it? Or are you still using 7.3?

They stopped printing manuals and now they are available online only. We are using 8.1.7 and 9.2.0.7.0. We are also having use of Sql Server 2008 R2.

Nagesh:

We are using 8.1.7 and 9.2.0.7.0. We are also having use of Sql Server 2008 R2.

Wow! 2008? That's pretty bleeding edge, don't you think?

Nagesh:

Cassidy:

Nagesh:

Oracle 7.3 manual tells you not to do it.

 

Do later manuals mention it? Or are you still using 7.3?

They stopped printing manuals and now they are available online only.

 

I presume you meant "documentation" when you said "manual", so my question could have been interpreted as "does documentation for later versions of Oracle still insist on this rule?"

Simply put, the "@" in a password is like a space in a filename - you'll need to escape it or hide it in some way so it doesn't get misinterpreted, but it's not a banned character. Just a nuisance one.

 

Cassidy:

Nagesh:

Cassidy:

Nagesh:

Oracle 7.3 manual tells you not to do it.

 

Do later manuals mention it? Or are you still using 7.3?

They stopped printing manuals and now they are available online only.

 

I presume you meant "documentation" when you said "manual", so my question could have been interpreted as "does documentation for later versions of Oracle still insist on this rule?"

Simply put, the "@" in a password is like a space in a filename - you'll need to escape it or hide it in some way so it doesn't get misinterpreted, but it's not a banned character. Just a nuisance one.

 

 

 

I did some checking and testing. You can have @ character in the password. Just do not use sql plus to make connection. If your client is java or vb or vc++, the password will work and you'll make valid connection to the database. It appear that the warning was put in there for dba who frequently used to use sqlplus tool to make connections.

 

Nagesh:

You can have @ character in the password. Just do not use sql plus to make connection.

 

Incorrect.

Nagesh:

It appear that the warning was put in there for dba who frequently used to use sqlplus tool to make connections.

No, the warnings were there for people who didn't understand that it was a reserved character and needed to be escaped. That's Oracle being patronising by making out it can't be used when it can in certain circumstances.

 

Cassidy:

Nagesh:

You can have @ character in the password. Just do not use sql plus to make connection.

 

Incorrect.

Nagesh:

It appear that the warning was put in there for dba who frequently used to use sqlplus tool to make connections.

No, the warnings were there for people who didn't understand that it was a reserved character and needed to be escaped. That's Oracle being patronising by making out it can't be used when it can in certain circumstances.

 

 

 

I am basing this on my own testing. I have oracle database to practice this on. Do you? Make user. Create password with "@" character. Then use java to make connection.

It will make successful connection. What part of that is incorrect? I am going to make guess and state taht you're refer to my second comment. You can make sqlplus to make connection.

 

 

Nagesh:

You can have @ character in the password. Just do not use sql plus to make connection.

 

Nagesh:

You can make sqlplus to make connection.

Make your mind up...

Cassidy:

 

Nagesh:

You can have @ character in the password. Just do not use sql plus to make connection.

 

Nagesh:

You can make sqlplus to make connection.

Make your mind up...

I am saying if you use "@" in your password, you SHOULD not sure sqlplus to make a connection, but you can if you want to go for stuff like escape sequences.

Better and more accurate, yup.