2

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?

Improve this question
3
  • 1
    Possible duplicate of Are the ALL_OBJECTS and DBA_OBJECTS different when you have DBA Role?
    – topshot
    Commented Apr 5, 2019 at 18:34
  • 1
    @topshot the suggested duplicate, despite being the accepted answer, doesn't actually answer the question
    – mustaccio
    Commented Apr 8, 2019 at 13:05
  • 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
    – mustaccio
    Commented Apr 8, 2019 at 13:14

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.