Sunday, September 20, 2020
    Home Oracle DBA Tips Create Schema in Oracle 18c XE

    Create Schema in Oracle 18c XE

    Article explain how to create new user in Oracle 18c XE and grant some system privileges with connect as new schema user in database.

    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/[email protected]: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.

    Consider Reading to these articles:

    - Advertisement -
    - Advertisment -

    Most Popular

    ORA-01194: file 1 needs more recovery to be consistent

    The blog post explains how to restore and recover database using until cancel with error ORA-01194

    How to enable Archivelog

    This blog post explains how to enable archive log mode in Oracle database for newest versions.

    Shared vs Static Library Performance

    The article explains the benefits of a shared library and static library usage in application building for improving application performance.

    Migration methods of Oracle Database

    Blog post explains which tricks and methods are simple to perform migration of small and large Oracle database

    Recent Comments