How to Find / List Available Schema in Oracle



Share this article :

Use below command to query for any available schema (except for SYSTEM and SYSAUX) in your Oracle database system.

From the query below, it’s show that the Oracle database is having 2 schema configured.

SQL> SELECT username from dba_users where default_tablespace 
not in ('SYSTEM','SYSAUX');

USERNAME
------------------------------
SAPSR3DB
SAPSR3

Alternatively, you can use below queries.

### QUERY 1 ###

SQL> select * from all_users;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------------
SYS                                     0 24-OCT-15
SYSTEM                                  5 24-OCT-15
OUTLN                                   9 24-OCT-15
DIP                                    14 24-OCT-15
ORACLE_OCM                             21 24-OCT-15
DBSNMP                                 30 24-OCT-15
APPQOSSYS                              31 24-OCT-15
SAPSR3                                 34 24-OCT-15
SAPSR3DB                               38 24-OCT-15
### QUERY 2 ###

SQL> select distinct owner from dba_segments where owner 
in (select username from dba_users where default_tablespace 
not in ('SYSTEM','SYSAUX'));

OWNER
------------------------------
SAPSR3
SAPSR3DB
### QUERY 3 ###

SQL> SELECT username FROM all_users ORDER BY username;

USERNAME
------------------------------
APPQOSSYS
DBSNMP
DIP
ORACLE_OCM
OUTLN
SAPSR3
SAPSR3DB
SYS
SYSTEM

9 rows selected.

Related posts

Set Oracle to NOARCHIVELOG mode
Find the Location of Oracle SPFILE
How to Start & Stop Oracle iSQL*Plus
ORA-00376: file 2 cannot be read at this time
© 2017 ITsiti. All Rights Reserved
Powered by KEEM