I am trying to figure out the list schema created in a database, I came across many answers like this and this which are trying to tell either use dba_segments or use dba_users.
But when I use those in my database then results have substantial difference.
I am looking for answers explaining which one is correct (dba_segments or dba_users) and why, so please do not think that my question is "how to get a list of all available schema in database".
1 Answer
dba_segments shows SEGMENTS - which are owned by schemas
you can have a schema that has no segments - objects that use segments can generally be thought of as tables or indexes. A user could own a synonym or a PL/SQL unit but have no segments for example.
Here's a list of segment types for my 12c system
HR@orcl🍻🍺 >select distinct segment_type from dba_segments; SEGMENT_TYPE LOBINDEX INDEX PARTITION ROLLBACK NESTED TABLE TABLE PARTITION LOB PARTITION LOBSEGMENT INDEX TABLE CLUSTER dba_users will show you EVERY user in the database, whether they own 'data' or not
here's how to find SCHEMAS with no segments, or one way
HR@orcl🍻🍺 >select distinct username 2 from dba_users 3 minus 4 select distinct owner 5 from dba_segments; USERNAME ANONYMOUS APEX_LISTENER APEX_PUBLIC_USER APEX_REST_PUBLIC_USER APPQOSSYS BASIC_PRIVS BI... 
