regarding oracle database "sysdba" user and creating a new user

Question

I am very much new to oracle database, please forgive me for any technical errors in my question. I am using oracle 19c and I think my password for "system" user is different than the password for "sysdba". At the time of installation I used the same password every time but now while connecting to system or "\ as sysdba" , there is no problem connecting but when I type the password for "orcl_listener" or "orcl" or "sysdba" , the password is wrong . for example: conn sysdba Enter password: ERROR: ORA-01017: invalid username/password; logon denied

when I create a new connection in SQL Developer, I use a username as a system, and the password is correct. success when I create a new connection in SQL Developer, I use username as \ as sysdba and password is correct .success when I create a new connection in SQL Developer, I use username as sysdba and password is correct .not success and error says Status: Failure -Test failed: ORA-01017: invalid username/password; logon denied .I am such a new user, if possible please provide me basic details of oracle databases in the simplest ways.

---

Now the second problem is that after creating a new connection in SQL developer using system username as its password is working, I expanded connection and at the bottom, I right-clicked on other users and created a new user with username "hr" and here it says "ORA-65096: invalid common user or role name".when I used c## as prefix no error occurs but I don't want any c##, it means it has to be a local user but where is option to create a local user? please help me.

Answer 1

To understand common users and local users you have first to understand multitenant architecture in Oracle: container database (CDB) and pluggable databases (PDB).

In multitenant architecture you can only create a local user in a PDB.

To create a local user connected with SQL*Plus as user SYS:

Check your current CDB/PDB

show con_name

List existing PDBS

show pdbs

Go to an existing PDB:

alter session set container=mypdb;

Check your current PDB

show con_name

Create a local user in the current PDB and grant some basic privileges:

create user myuser identified by "mypwd" quota unlimited on tablespace myts;
grant create session to myuser;
grant create table to myuser; 

To connect directly with SQL*Plus to this PDB you must use the Oracle Net service for this PDB:

sqlplus myuser/mypwd@myhost:myport/mypdb