無法將新使用者連接到可插拔數據庫

我是 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

這是 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.

引用自：https://dba.stackexchange.com/questions/292230