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:
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:
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
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:
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;
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;
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.
This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.