Oracle DB what is the difference between ALL, USER and DBA Objects?

Question

As the title suggests, I am trying to understand the structure of our Oracle DBs a little more and a little confused by the ALL, DBA & USER Objects.

A quick SQL

select count(*) ALL_OBJS from all_objects where owner = 'XXXX';
select count(*) DBA_OBJS from dba_objects where owner = 'XXXX';
select count(*) USER_OBJS from USER_OBJECTS; (logged on as XXXX)

Returns:

 ALL_OBJS
----------
 1591
 DBA_OBJS
----------
 1632
 USER_OBJS
----------
 1632

As I presume our user has DBA rights and the USER objects and DBA objects are the same? But what are ALL objects? If you are looking to clear out a User's SCHEMA which would you remove from or would you drop objects from all three?

• topshot: Possible duplicate of Are the ALL_OBJECTS and DBA_OBJECTS different when you have DBA Role?
• mustaccio: @topshot the suggested duplicate, despite being the accepted answer, doesn't actually answer the question
• mustaccio: If you try to identify objects that don't appear among DBA_OBJECTS it might give you a hint about the reasons: select owner, object_name, object_type from dba_objects minus select owner, object_name, object_type from all_objects

Answer 1

• USER_OBJECTS are your objects, i.e. the objects where the current user is the owner
• ALL_OBJECTS are all objects which you can access. These are of course your own objects from above plus objects from other schemas where access is granted to you.
• DBA_OBJECTS are all objects in the database. As a DBA you have access to all objects, thus ALL_OBJECTS and DBA_OBJECTS should be the same if you are connected as DBA.

If you want to remove all objects from a user then the best command would be DROP USER xyz CASCADE;

Do not delete anything from XXX_OBJECTS view. Use DROP ... commands to removed those objects.