And then, as a normal user (not root or oracle user) I run the following command:
> sqlplus system
Then input my password for the user 'system' and then it successfully gives me the SQL prompt.
However, if I try this command:
> sqlplus system@borg
> sqlplus firstname.lastname@example.org
It asks me for the password and then gives the following error again:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
Got any clues why?
01-21-2014, 03:27 PM
I struggled, and struggled, and struggled with this problem. I eventually figured out what was going on. I had installed both server and client on the same machine. This results in two Oracle directories - one for client and one for server. This also results in TWO tns_names directories. Likely the tns_names directory in your client folder will be EMPTY. Thus, any client you use (including java code) can't connect as there is no tnsnames.ora file to use. It won't use the server tnsnames.ora file - it will look for a tnsnames.ora file in the client directory instead and give up when it's not there. When you connect with SQLPLUS, it's bypassing the tnsnames.ora file.
You've got two choices to fix this problem. One is to simply copy the tnsnames.ora and listener.ora file from the server directory to the client directory. For example, on my system the directories are as follows:
The better solution is to define the Windows Environment variable TNS_NAMES and enter your server tns_names directory path. This way, you don't have to copy the tnsnames.ora and listener.ora file over to the client folder each time you make a change.