Re: Dropping all tables of a user

tzagara_at_tigris.cti.gr wrote:
: Can anybody tell me, how can I drop all the tables of user, who does not
: use the database any moore. Also, when I revoke a user, is the entry in
: all_users table deleted ?

How about :

If you have already revoked connect from the user then :

   sqlplus system/<password>
   grant connect,resource to <username> identified by dummy ;    connect <username>/dummy

otherwise, just :

   sqlplus <username>/<password>

Then, while in SQL*Plus :

   set pagesize 0
   set feedback off
   select 'drop '||tabtype||' '||tname||';'    from tab
   where tabtype <> 'INDEX'

   spool drop.sql
   /
   spool off
   start drop
   exit

(Ignore the SQL> error messages - can't remember how to suppress these).

The above SQL should just run through each of the views and tables belonging to the user and drop them.

Then you can revoke access from the user. I don't think revoking actually removes the entry from the all_users table.

Hope this is of help.

             Terry Moffitt - BT Belfast Engineering Centre
             tmoffitt_at_bfsec.bt.co.uk        +44 232 894436

All opinions expressed are my own.