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.
