Oracle 10g: ORA-12154 when connect to database with newly created user

Question

This is the continue topic with this topic:

Oracle 10g: Error when creating database manually by scripts

Basically, I had created an Oracle database named "testdb" using only batch scripts and SQL scripts.

After successfully creating a database using script, I create a script to create user for client connect to the database.

CreateUser.bat

sqlplus sys/test as sysdba @D:\Script\CreateUser.sql

CreateUser.sql

shutdown immediate;
startup;
CREATE USER usr1 IDENTIFIED BY usr1
    DEFAULT TABLESPACE users
    QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, GRANT ANY privilege TO usr1;
exit;

Everything run ok, with no error.

Then I try to test by connecting to SQLPlus on cmd:

sqlplus usr1/usr1@testdb

This retrun the error:

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

I wonder what I did wrong.

The same happen for the database I created via DBCA.

Is testdb an alias in your tnsnames.ora? The error suggests it is not. You can put it there manually or use the net manager tool. But if the database is local, and you have ORACLE_HOME and ORACLE_SID set properly already, why aren't you just connecting locally now? Why go through TNS at all? It isn't anything to do with the user, anyway, the error is complaining about the connect identifier, which is the testdb part. — Alex Poole
I have no idea what you are talking about :D. I am really unfamiliar with Oracle, so I am not sure about TNS, but during my database creating, I did not come across any of it setting. You can check my link at the top to see the script I used to create database [testdb] — N.D.H.Vu
Very, very short version: try sqlplus usr1/usr1. The @testdb attempts to use an alias defined in tnsnames.ora to access the database over SQl*Net. You don't need to do that if you're connecting from the same machine the database is on. — Alex Poole
Thanks. I had done it before, and I know that it works. However, the problem is that I feel really uncomfortable with that kind of connection. Let say I have another database name [testdb_2] that has same structure as the [testdb] but with different set of data. When I connect with "sqlplus usr1/usr1", and type INSERT [something]. How can I know which database the script will run on. — N.D.H.Vu
It depends on your ORACLE_SID setting. There are ways to change your SQL*Plus prompt to show you the database name which can be a useful visual clue. But back to your question... you should read up on naming methods. With 'easy connect' you can do sqlplus usr1/usr1@//hostname:port/testdb1, where hostname either localhost or the servers real name depending on how things are configured, 1521 is the default listener port, and testdb1 is the service name. Do lsnrctl status to verify what those should be. — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2014-06-02T11:10:47

The error is because you don't seem to have an entry for testdb in your tnsnames.ora file. You could add one, manually or with the configuration tools netca etc); or you could bypass that with the 'easy connect' syntax:

 sqlplus username/password@//hostname:port/service_name

If this is on your local machine, the default port and the service name matches the SID, that could be simolified a bit to:

sqlplus usr1/usr1@//localhost/testdb

You can verify the service name in v$parameters, or with lsnrctl services.

You can read more about connections and naming methods.

If your are connecting to a database on the same machine as the client, and you are using the same ORACLE_HOME, you don't need to use TNS/SQL*Net at all. With ORACLE_SID set to the right value, just doing this will connect locally:

sqlplus usr1/usr1

Oracle 10g: ORA-12154 when connect to database with newly created user

2 Answers