Bert Swope
Oracle Database
Well, this is one of those circumstances, where your ability to answer this question will depend upon your user’s assigned security roles and what you actually want.
To get a complete list, you will need to use the DBA_ administrator tables to which most of us will not have access. In the very simple examples below, you may want to add a WHERE clause to eliminate the system schemas from the list, like ‘SYS’ and ‘SYSTEM,’ if you have access to them.
Example Administrator (DBA) Schema List
SELECT distinct OWNER as SCHEMA_NAME
FROM DBA_OBJECTS
ORDER BY OWNER;
Example Administrator (DBA) Schema List Results Screenshot
Example Administrator (DBA) Schema List Results Screenshot
Fortunately for the rest of us, there are All user tables, from which we can get a listing of the schemas to which we have access.
Example All Users Schema List
SELECT distinct OWNER as SCHEMA_NAME
FROM ALL_OBJECTS
ORDER BY OWNER;
Example All Users Schema List Results Screenshot
Example All Users Schema List Results Screenshot
