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

Question

I am using Toad to connect my database and its working fine for me . But when i try to connect with the same credential , i am getting the error message as shown below:

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

Steps:

1. Opened command promt
2. Reach to the path of SQLPLUS (C:\programs\Oracle\Ora11g\BIN)
3. C:\programs\Oracle\Ora11g\BIN\sqlplus username@schema_name

4. Enter Password :

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

5. But when i open this with the owner of the schema ,it works for me

6. C:\programs\Oracle\Ora11g\BIN\sqlplus schema_name@schema_name

7. Enter Password:

8. Connected to :......................

So,my question is why my username is not able to connect that particular schema ,while the owner of that schema can connect . Note:I can connect to that schema with my username with Toad ,but not with sqlplus

My TNSNAME.ORA is under TNS_ADMIN folder

Please help me out of this .

Answer 1

actually through toad u are able to login because ORACLE_HOME was set there. but once your trying to use $ORACLE_HOME/bin, then your home is not set properly.

To resolve this, first export $ORACLE_HOME and $ORACLE_SID then try, it should be fine.

Answer 2

So you don't need to specify the full path when you run sqlplus.exe:

Set your %TNS_ADMIN% variable to the full path of the relevant folder (C:\programs\Oracle\Ora11g\TNS_ADMIN ?). Instructions for doing so are a google away. At the same time, make sure %ORACLE_HOME% is set to C:\programs\Oracle\Ora11g and that your %PATH% has C:\programs\Oracle\Ora11g\bin added to it.

The problem you're actually having is because of the parameters you're passing to sqlplus.

You've been doing:

C:\programs\Oracle\Ora11g\BIN\sqlplus username@schema_name

Which is wrong & interpreted by sqlplus as:

C:\programs\Oracle\Ora11g\BIN\sqlplus username@database_name

Also, in Oracle, the owner of a schema (hence the schema name) is the user name. Obviously, grants can be given to other users to perform DML/DDL on other schemas, but you still have to login with a user with sufficient rights. (Proxy logins exist, as does alter session set current_schema=blah, but both are beyond the scope of this answer).