DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2001


    Hi all,

    I have 400 users created on my Oracle DB (ver. 8) on Unix. I want to drop 25 Users out of 400, before that i want a list of users who are having Object privileges on these 25 users objects (means objects owned by these 25 users).

    Please can any one give me a good query for getting the above result.



  2. #2
    Join Date
    May 2002
    You can do this step-by-step, or put it all together at once after some prepping.

    First, you need to collect the users you want to drop. For 25 users, I'd lock their accounts with

    alter user username account lock;

    Check on the list of locked accounts - there may be some you don't want to drop

    select username from dba_users where account_status = 'LOCKED';

    To see what the soon to be dropped users have as objects:

    select owner, object_name from dba_objects, dba_users
    where owner in (select username from dba_users where account_status = 'LOCKED');
    order by owner;

    select grantor, grantee, table_name from user_tab_privs_made, dba_users where grantor in (select username from dba_users where account_status = 'LOCKED')
    order by grantor;

    That should be enough to get you started.

  3. #3
    Join Date
    Mar 2001
    Hi Stecal,

    Thanks for reply It's a great help.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.