Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 .

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["                  <li><a href=\"//area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2015 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2015.4.1.2434\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Database Administrators Stack Exchange works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class="], "copyright__copyright__copyright": ["s 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).\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}