Create Database Schema (USERS) in Oracle 18c XE

In previous articles reference was made to schema such as SYSTEM, SYS, PDBADMIN and HR, these schema are associated with users of the same name in any version or edition of Oracle, however, the introduction of the Multitenant feature (Oracle 12c and higher), has established a new type of user s called common users , that is, users that are created in a container database or CDB and are automatically replicated to PDBs.

This new type of account requires using C ## or c ## suffixes, that is, this would be its flag, for example, connected as SYS or SYSTEM in the CDB database:

- Advertisement -

create user C##PROGRAMS
identified by password
default tablespace USERS;

We grant privileges to the user created with the grant command as following:

grant connect, resource, create sessions to C##PROGRAMS;

Now we list the users that start with C:

- Advertisement -

select username from all_users
where username like'C% ';

Those users whose names do not start with C ## are local users . We connect as C ## PROGRAMS incontainer or CDB

SQL>conn C##PROGRAMS/password@localhost:1521/XE

We create a table using following commands.

create table program (
program_id number (6) not null,
program_name varchar2 (120) not null,
constraint pk_program primary key (id_program)
);

We select the tables of the current user in the XE container:

select table_name
from user_tables;

And the name of the table created will be displayed.

We change to the PDB XEPDB1 , but we remain connected as C##PROGRAMS:

alter session set container = XEPDB1;

select table_name
from user_tables;

When executing this selection statement we verify that there are no associated tables in the C##PROGRAMS account of XEPDB1.

To return to the container we execute

alter session set container = CDB$ROOT;

These statements can be executed using SQL * PLUS or SQL Developer. I hope this article is very useful for database technology lovers and the entire Oracle Database XE community.

- Advertisement -