主页 / dba / 问题 / 292230
Accepted
AxD
Asked: 2021-05-26 06:55:08 +0800 CST

无法将新用户连接到可插拔数据库

  • 772

我是 Oracle DB 的新手（来自 MS SQL Server）。

首先，我为 Oracle Database 18c XE 下载了一个 Docker 容器。

当我运行该容器时，在创建可插入数据库后，我似乎无法与我创建的新用户连接：

无法连接

据我所知，我提供了所有必要的信息，让这些用户做好准备。但我错过了什么？我不明白，即使在阅读了几十个文档和帮助页面之后。

这是lsnrctl status, 在容器中运行的输出：

> lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 25-MAY-2021 11:57:33

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                25-MAY-2021 11:11:15
Uptime                    0 days 0 hr. 46 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/5854b834e19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=5854b834e19c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "c323f0bfe62f0aaee053030011ac1ec5" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "c323f7dc51bd0b68e053030011acc9a8" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "mobydick" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

这是我的tnsnames.ora文件内容：

# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))  

XEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XEPDB1)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))    
     )
     (CONNECT_DATA =
       (SID = PLSExtProc)
       (PRESENTATION = RO)
     )
  )



mobydick =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mobydick)
    )
  )

我创建了类似于这样的可插拔数据库：

ALTER SESSION SET CONTAINER=CDB$ROOT;

CREATE PLUGGABLE DATABASE "mobydick" ADMIN USER "sa" IDENTIFIED BY "abc123" ROLES=(dba) FILE_NAME_CONVERT=('/opt/oracle/product/18c/dbhomeXE/dbs/mobydick/') STORAGE UNLIMITED

ALTER PLUGGABLE DATABASE mobydick OPEN READ WRITE
oracle plsql

1 个回答

  1. Best Answer

    这是 SQL Developer 生成的 SQL 语句：

    CREATE USER "test" IDENTIFIED BY "abc123" ;

    这使得用户名区分大小写，因此实际的用户名可以用作"test".

    您尝试使用用户名登录 SQL Developer test。这不是区分大小写的语法，它会自动被视为"TEST"，但该用户不存在。

    对用户名使用区分大小写的语法，因此不要test提供，而是"test"作为用户名提供。

    顺便说一句，我们几乎从不使用区分大小写的用户名或对象名称。您可以只创建不带双引号的用户，并且您的连接尝试将起作用：

    CREATE USER test IDENTIFIED BY "abc123" ;

    鉴于我想通过 使用小写用户名sqlplus进行连接，例如，我必须输入什么才能连接为“test”？我似乎无法在CONNECT子句中使用引号。

    "是 bash shell 中的特殊字符，您需要对其进行转义以在命令行参数中使用它：sqlplus \"test\"/abc123@mobydick. 该connect命令可以很好地处理双引号：sqlplus /nolog然后在SQL>提示符下， connect "test"/abc123@mobydick.

    • 2

相关问题