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

ORA-01161: db name A in file header does not match given name of B
Grant & Revoke User Privileges in Oracle
How to Uninstall Oracle 10g (Windows)
How to Determine Oracle Database Character Set
© 2017 ITsiti. All Rights Reserved
Powered by KEEM