How To Get A List Of Oracle Database Schemas?

Well, this is one of those circumstances, where your ability to answer this question will depend upon your user’s assigned security roles and what you actually want. 

To get a complete list, you will need to use the DBA_ administrator tables to which most of us will not have access.  In the very simple examples below, you may want to add a WHERE clause to eliminate the system schemas from the list, like ‘SYS’ and ‘SYSTEM,’ if you have access to them.

Example Administrator (DBA) Schema List

SELECT distinct OWNER as SCHEMA_NAME

FROM  DBA_OBJECTS

ORDER BY OWNER;

Example Administrator (DBA) Schema List Results Screenshot

Fortunately for the rest of us, there are All user tables, from which we can get a listing of the schemas to which we have access.

Example All Users Schema List

SELECT distinct OWNER as SCHEMA_NAME

FROM    ALL_OBJECTS

ORDER BY OWNER;

Example All Users Schema List Results Screenshot

Related References

Oracle help Center > Database> Oracle > Oracle Database > Release 19

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;

How to Determine Your Oracle Database Name

Oracle provides a few ways to determine which database you are working in.  Admittedly, I usually know which database I’m working in, but recently I did an Oracle Database Express Edition (XE) install which did not goes has expected and I had reason to confirm which database I was actually in when the SQL*Plus session opened.  So, this lead me to consider how one would prove exactly which database they were connected to.  As it happens, Oracle has a few ways to quickly display which database you are connected to and here are two easy ways to find out your Oracle database name in SQL*Plus:

  • V$DATABASE
  • GLOBAL_NAME

Checking the GLOBAL_NAME table

The First method is to run a quick-select against the GLOBAL_NAME table, which. is publicly available to logged-in users of the database

Example GLOBAL_NAME Select Statement

select * from global_name;

Checking the V$DATABASE Variable

The second method is to run a quick-select a V$database. However, not everyone will have access to the V$database variable.

Example V$database Select Statement

select name from V$database;

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;