Monday, November 23, 2020
dbametrix
More
    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 -
    dbametrix

    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 -
    dbametrix

    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 -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    Oracle Scheduler Jobs Example

    Blog post explains how to configure job scheduler in Oracle by example with how to manage, delete, drop, disable and enable jobs with how to check history of job if broken and enable.

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    Recent Comments