remote dba support
    HomeOracle DBA TipsCreate 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/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.

    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.

    - Advertisement -
    - Advertisment -
    remote dba services

    Most Popular