Oracle Express Edition Error – ORA-65096: invalid common user or role name

While trying to create user in Oracle Database 18c Express Edition I kept getting an “ORA-65096: invalid common user or role name” error, which didn’t make sense to me so after validating my command, that I was signed in as an admin user, and determining that my “CREATE USER” was formatted correctly.  I did some additional research and determined that in the hidden parameter “_ORACLE_SCRIPT” needed to be set to “True” starting with the Oracle Version 12c and higher.

Setting the “_ORACLE_SCRIPT” values

To set the “_ORACLE_SCRIPT” hidden variable to “True” you need to run an “Alter” command. Then you will be able to create the desired user and run your grants commands as usual.

Alter SQL Command

alter session set “_oracle_script”=true;

Oracle Database – Useful links

Oracle Database
Oracle Database

Some useful oracle documentation references.

Reference Type Link

Database SQL Language Reference (11.2)

https://docs.oracle.com/cd/E11882_01/server.112/e41084/

Database SQL Language Reference (11.2)

https://docs.oracle.com/cd/E11882_01/server.112/e16604/toc.htm
Database SQL Language Reference (12c) https://docs.oracle.com/database/121/SQLRF/toc.htm

Database (12c) SQL*Plus User’s Guide and Reference

https://docs.oracle.com/database/121/SQPUG/toc.htm

SQL Developer Documentation

http://docs.oracle.com/cd/E12151_01/

Oracle SQL – How to limit the number of rows returned by a select query

Oracle doesn’t recognize the ’Limit’ command, however, there is any easy way to achieve the same result.  Using the Oracle/function in the ‘where’ clause.

ROWNUM Pseudo Column Description

The Oracle/PLSQL ROWNUM pseudo column returns a number that represents the order that a row is selected by Oracle from a table or joined tables. The first row has a ROWNUM of 1, the second has a ROWNUM of 2, and so on.

Basic RUWNUM Syntax

SELECT column_name(s)

FROM Schema.table_name

WHERE ROWNUM <= number

Order by column_name(s);

Example SQL Limiting to One row

The one row limit has two accurate way to get the desired result:

·       SQL Limiting to One row using equal to

SELECT RSNBL_CODE,

MVNG_AVG1_PRD

FROM Blog.SCP_FCST_CONTROL

where rownum = 1;

·       SQL Limiting to One row using less than or equal to

SELECT RSNBL_CODE,

MVNG_AVG1_PRD

FROM Blog.SCP_FCST_CONTROL

where rownum <= 1;

Example SQL Limiting to multiple rows

SELECT RSNBL_CODE,

MVNG_AVG1_PRD

FROM Blog.SCP_FCST_CONTROL

where rownum <= 10;