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...
10

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.