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.
where default_tablespace not in ('SYSTEM','SYSAUX')
owner not in (