In this short article, I will give a brief idea about how to create a local user in a pluggable database and how to check its status as well.
Basically, with this multitenant architecture concept, there are two types of users common user and local user, common users are shared across multiple pluggable databases while local user is specific to a particular database, here we will discuss how to create a local user
SQL> alter session set container=technopdb;
Session
altered.
SQL>
create user techno_user identified by test123;
User
created.
SQL>
grant create session to techno_user;
Grant
succeeded.
SQL>
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
3 TECHNOPDB READ WRITE NO
SQL>
SQL>
select username, con_id from cdb_users where username='TECHNO_USER';
USERNAME CON_ID
-------------------------
----------
TECHNO_USER 3
SQL>
select d.username, c.con_id,p.name from dba_users d, cdb_users c , v$pdbs p
where d.username='TECHNO_USER' and d.username=c.username and p.con_id=c.con_id;
USERNAME CON_ID NAME
-------------------------
---------- --------------------------------------------------------------------------------------------------------------------------------
TECHNO_USER 3 TECHNOPDB
SQL>
Here we can check, how to connect to pluggable database using local user
if you are trying it from your local machine/desktop then below command will work fine, you just need to have ip address and port number and if you are using tnsnames then mention the in the connection string
C:\Users\****>sqlplus
techno_user/test123@192.168.0.107:1521/technopdb
SQL*Plus:
Release 19.0.0.0.0 - Production on Tue Jun 15 16:29:48 2021
Version
19.3.0.0.0
Copyright
(c) 1982, 2019, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.3.0.0.0
SQL>
SQL>
select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TECHNOPDB
SQL>
show user
USER is "TECHNO_USER"
SQL>