Oracle 10g：ORA-12154当用新创建的用户连接到数据库时 [英] Oracle 10g: ORA-12154 when connect to database with newly created user

问题描述

这是与此主题的continue主题：

基本上，我创建了一个Oracle数据库仅使用批处理脚本和SQL脚本命名为testdb。

使用脚本成功创建数据库后，我创建一个脚本来创建客户端连接到数据库的用户。 / p>

CreateUser.bat

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

CreateUser.sql

  shutdown immediate; 
 startup; 
创建用户usr1标识符usr1 
默认TABLESPACE用户
 QUOTA无限用户; 
 GRANT CREATE SESSION，GRANT ANY特权TO usr1; 
 exit; 
  

一切正常，没有错误。

然后我尝试通过连接到SQLPlus在cmd上测试：

  sqlplus usr1 / usr1 @ testdb 
  

重新运行错误：

  ORA-12154：TNS：无法解析指定的连接标识符
  

。

通过DBCA创建的数据库也会发生同样的情况。

解决方案

p>错误是因为您的 tnsnames.ora 文件中似乎没有 testdb 的条目。您可以手动或使用配置工具 netca 等）添加一个;或者您可以使用easy connect语法跳过此操作：

  sqlplus username / password @ // hostname：port / service_name b $ b  

如果这是在本地计算机上，默认端口和服务名称匹配SID， simolified有点：

  sqlplus usr1 / usr1 @ // localhost / testdb 
  

您可以在 v $ parameters 中验证服务名称，或使用 lsnrctl services 。

您可以详细了解连接和命名方法。

如果您连接到同一台机器上的数据库作为客户端，并且您使用相同的 ORACLE_HOME ，则不需要使用TNS / SQL * Net。 ORACLE_SID 设置为正确的值，只需这样做即可在本地连接：

  sqlplus usr1 / usr1 
  

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.

解决方案

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当用新创建的用户连接到数据库时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！