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

connectivityoraclesqlplus

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:

Opened command promt
Reach to the path of SQLPLUS (C:\programs\Oracle\Ora11g\BIN)
C:\programs\Oracle\Ora11g\BIN\sqlplus username@schema_name
Enter Password :

ERROR:
ORA-12154 TNS: could not resolve the connect identifier specified
But when i open this with the owner of the schema ,it works for me
C:\programs\Oracle\Ora11g\BIN\sqlplus schema_name@schema_name
Enter Password:
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 .

Best Answer

Actually through toad you are able to login because ORACLE_HOME was set there. but once you're trying to use $ORACLE_HOME/bin, your home is not set properly.

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

Related Solutions

Oracle TNS names configuration

There are various methods of achieving this ,one of them as suggested by Yasir. Please follow the below steps:

Close toad/sqlplus
Create a folder TNS_ADMIN
Add tnsnames.ora in TNS_ADMIN
Add TNS_ADMIN in enviornment variable.
Open Toad/Sqlplus and login

I think this will be very clear ,cheer

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

When you use sqlplus userName/myPassword@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=asdasdasd.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))" then you don't need any tnsnames.ora file.

Your entry in tnsnames.ora file should be like this:

AARCER1 = (DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST= asdasdasd.com)(PORT=1524))
    (CONNECT_DATA =
      (SID = AARCER1)
    )
  )

Then you can shorten your command to sqlplus userName/myPassword@AARCER1 - that is the purpose of tnsnames.ora.

In order to use tnsnames.ora you must also have file sqlnet.ora. There you need an entry like this:

NAMES.DIRECTORY_PATH = (TNSNAMES)

However, default value is NAMES.DIRECTORY_PATH=(tnsnames, ldap, ezconnect), so actually you can skip it.

Set environment variable TNS_ADMIN to location of these files, i.e.

export TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin

Best Answer

Related Solutions

Oracle TNS names configuration

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

Related Question