Working With SCOTT & TIGER in Oracle Database 21c Express Edition on your local system

We are working with Multitenant Architecture. The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB). A CDB includes zero, one, or many customer-created pluggable databases (PDBs).

Once we install XE we always log in to cdb if you login with sys now we need to switch to pdb in cdb you are not allowed to create Scott we have to use some c##username we not going to talk about that, we are coming straight at the point.

once you login in sqlplus using sys as sysdba

C:\Users\ambi>sqlplus sys as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Feb 12 11:10:21 2022 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0

SQL> select username from dba_users where username='SCOTT';

no rows selected 

 

SQL> create user SCOTT identified by tiger;

create user SCOTT identified by tiger

            *

ERROR at line 1:

ORA-65096: invalid common user or role name

Where is the problem?

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 XEPDB1                         READ WRITE NO

once we got connected with sys We have to connect with a pluggable database like XEPDB1

SQL> alter session set container = XEPDB1;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

XEPDB1

SQL> select username from dba_users where username='SCOTT';

no rows selected
we don't have scott in pdb now we will create

SQL> create user SCOTT identified by tiger;

User created.

grant permissions

SQL> grant create session to scott;

Grant succeeded.

SQL> grant connect, resource to scott;

Grant succeeded.

SQL> grant create any view to scott;

Grant succeeded.

checking for user exits or not in pdb

SQL> select username from dba_users where username='SCOTT';

USERNAME

----------------------------------------

SCOTT

now going to connect with scott

SQL> conn scott/tiger@192.168.0.198:1521/XEPDB1

Connected.

If you don't know what is your address you can check in your oracle net manager just see the address write it down and close the window without saving anything.

now going to create demo tables
if you don't know how to create demo tables you can read my article on this blog click me please.

SQL> @C:\DEMOBLD7.SQL

Building Oracle demonstration tables.  Please wait.

SQL> SELECT Tname FROM TAB;

TNAME

--------------------------------------------------------------------------------

DEPT

EMP

BONUS

SALGRADE

DUMMY

CUSTOMER

ORD

ITEM

PRODUCT

PRICE

SALES

11 rows selected.

Happy Learning.

This line is very important conn scott/tiger@192.168.0.198:1521/XEPDB1 otherwise you can't connect.

Please comment with your suggestions I will try to improve this article.

Disclaimer: These suggestions are only for local database installation in your own pc/laptop to learn and practice new concepts. don't try this in your production environment.