Question: I am trying to
write a query that will display all schemas in my Oracle database.
A schema owner is a user ID, but it is not necessarily a "real" schema,
a user that has populated tables. How do I write a query to
display all of my schemas?
Answer: It's not easy to
identify schema owners in any relational database, but if you make a few
assumptions, you can write a dictionary query to display all schemas. The
query below will extract all of the schema
names from the Oracle data dictionary.
select distinct
owner
from
dba_segments
where
owner in
(select username
from dba_users
where default_tablespace not in ('SYSTEM','SYSAUX')
)
;
or
select distinct
owner
from
dba_segments
where
owner not in (
'SYSTEM', 'XDB',
'SYS', 'TSMSYS',
'MDSYS', 'EXFSYS',
'WMSYS', 'ORDSYS',
'OUTLN', 'DBSNMP');
