0

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

Community
  • 1
  • 1
hagrawal7777
  • 14,103
  • 5
  • 40
  • 70

1 Answers1

4

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...
thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
  • Thank you for inputs. Ok, it means segments are like another database object? But when I use this query (`select * from ALL_OBJECTS where OBJECT_TYPE like '%SEGMENT%';`) then I do not see any segment, I am sure I having lot of segments in database. And if I understood your answer correctly then `dba_users` correctly lists all available schema/user in the database, please confirm? – hagrawal7777 Dec 13 '16 at 23:30
  • sorry @hagrawal - i amended my answer as you were leaving this comment - please refresh. and yes, if you want a list of users in a db, query DBA_USERS – thatjeffsmith Dec 13 '16 at 23:31
  • No problem at all. You answered perfectly, thank you for your time. – hagrawal7777 Dec 13 '16 at 23:34
  • One quick confirmation please: so that the schema listed using your 2nd query (how to find SCHEMAS with no segments) are essentially schema which doesn't contain any db object and data? – hagrawal7777 Dec 13 '16 at 23:53
  • right, it's a list of schemas that don't have any segments - which are defined here http://docs.oracle.com/cd/B19306_01/server.102/b14220/logical.htm#i10405 – thatjeffsmith Dec 14 '16 at 00:05
  • Ok, I got that it doesn't have any segment, but here I wanted to understand in layman's term that those schema are empty or in other words doesn't contain any data, right? – hagrawal7777 Dec 14 '16 at 00:07
  • no segments, no data - but what about an external table, where the data lies in a file on the OS and not in a tablespace? – thatjeffsmith Dec 14 '16 at 00:45
  • 2
    Just a note, you can specify clause `SEGMENT CREATION DEFERRED` (or set it even globally with parameter [DEFERRED_SEGMENT_CREATION](https://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams075.htm)). Then you may have tables or indexes but no segments as long as they don't contain any data. – Wernfried Domscheit Dec 14 '16 at 06:41
  • @WernfriedDomscheit Ok, so does it mean 1.a) whenever a table is created, a segment is automatically created, please confirm? 1.a) And also does same hold true for other db objects like views? 2) Suppose a table is created with `SEGMENT CREATION DEFERRED` clause then segment will not be created but also data cannot be inserted, and if someone is trying to insert data then it will result in some ORA-XXXX? – hagrawal7777 Dec 15 '16 at 20:48
  • 1
    1a correct (by default), a view does not occupy any space apart from code in system tsblespace. 2) Segments are created automatically as soon as first data is inserted into the table – Wernfried Domscheit Dec 15 '16 at 21:13