Search code examples
sqldatabaseoracle-databaseoracle-sqldeveloperoracle18c

Cannot create connection in SQL Developer with new user added in PDB


First of, I'm still new when it comes to Oracle DBs and how they tend to do stuff so please forgive if I'm failing to see something obvious. I spent around 5 hours troubleshooting following issue and can't seem to resolve it on my own. Would really appreciate any help I can get.

I'm running SQL Developer for Oracle XE 18c database on my personal PC, trying to learn APEX with it and I'm having issues creating connection to DB with new user, once new user is added. Relevant info:

  1. Able to connect to database using New connection prompt > sys as SYSDBA / password or system / password screenshot of successful test
  2. Must change from CDB to PDB (in my case to XEPDB1 with ALTER SESSION) if I want to add user
  3. If I try to add user either through SQL or GUI when in CDB - I get error ORA-65096: invalid common user or role name in oracle (meaning I have to switch to PDB - ok, no problem)
  4. Once I switch to PDB - I am able to add user properly
  5. User is simple name, simple pass, no way it can be missed, made 5-6 different accounts while trying, demo/demo, test/test, we'll use username: master / password: masterkey in this example
  6. Once I try to create New connection > master / masterkey I get Status : Failure -Test failed: ORA-01017: invalid username/password; logon denied, this is happening with every single user I created logon denied
  7. Same happens if I try logging through SQLPlus sqlplus success for system user

All the tutorials, explanations and answers I found, make it so simple to log in with system > create new user > create new connection with that same user. Simple as that, but not me. Now, I do see my created user under Other users section but I believe I should be able to have this user separately, as it's own connection, right? screenshot for that

I will appreciate every help I can get. Let me know if you need any other configuration data.

lsnrctl services output:


LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 09-MAY-2021 02:52:21

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-LU84249)(PORT=1521)))
Services Summary...
Service "33a4c118d1384fc5842519d99267b5b7" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:13 refused:0 state:ready
         LOCAL SERVER
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "XE" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:13 refused:0 state:ready
         LOCAL SERVER
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: DESKTOP-LU84249, pid: 7788>
         (ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-LU84249)(PORT=59728))
Service "xepdb1" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:13 refused:0 state:ready
         LOCAL SERVER
The command completed successfully```


Solution

  • Resolved by rebooting PC. Database was installed few hours earlier and I didn't reboot PC ever since, even with everything else properly configured. Connected successfully using service name XEPDB1 (should be default for XE DBs).