All we need is an easy explanation of the problem, so here it is.
I’m new to Oracle DB (coming from MS SQL Server).
For a start, I downloaded a Docker container for Oracle Database 18c XE.
When I run that container, after creating a pluggable database, I don’t seem to be able to connect with new users I create:
As far as I can see I provided all necessary bits and bites to get these users at the ready. But what am I missing? I don’t get it, even after reading dozens of doc and help pages.
Here’s the output of lsnrctl status
, run in the container:
> 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
And this is my tnsnames.ora
file content:
# 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)
)
)
I created the pluggable database similar to this:
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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
This is the SQL statement that SQL Developer generated:
CREATE USER "test" IDENTIFIED BY "abc123" ;
This makes the username case-sensitive, so the actual username can be used as "test"
.
You tried to login in SQL Developer using the username test
. This is not case-sensitive syntax, and it is automatically treated as "TEST"
, but that user does not exist.
Use the case-sensitive syntax for username, so instead of test
, provide "test"
as username.
By the way, we almost never use case-sensitive usernames or object names. You could just create the user without double quotes, and your connection attempt would work:
CREATE USER test IDENTIFIED BY "abc123" ;
Given that I wanted to connect using a lowercase user name by means of
sqlplus
, what would I have to enter to connect as "test", for instance? I don’t seem to be able to use quotes in theCONNECT
clause.
"
is special character in bash shell, you need to escape it to use it in commandline arguments: sqlplus \"test\"/[email protected]
. The connect
command handles double quotes just fine: sqlplus /nolog
then at SQL>
prompt, connect "test"/[email protected]
.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0