lwk@june.engin.umich.edu (Lewis W Kellum) (01/09/91)
Does any one have a set of sql commands to remove users completely from an Oracle system? Or just a list of the tables where records must be deleted. Thanks -- I want a GOOD reason with some moral backbone why Americans should die in the Mid East. Woody Kellum Internet: lwk@caen.engin.umich.edu
hthoene@doitcr.doit.sub.org (Hermann Thoene) (01/12/91)
In article <1991Jan8.204156.7959@engin.umich.edu> lwk@caen.engin.umich.edu writes: } Does any one have a set of sql commands to remove users } completely from an Oracle system? Or just a list of } the tables where records must be deleted. Thanks } Before you remove a user you must remove all his tables. The best thing to do that is to create a sql-script with following commands in SQL*Plus (this is for version 6, use V5 Data Dictionary table otherwise): set pagesize 999 spool drop_user select 'drop table '||object_name||';' from user_objects where object_type = 'TABLE'; spool off start drop_user.lst Run this script as the user you want to delete, the say 'revoke connect from USERNAME;' in SQL*Plus as a DBA. To write a general script for deleting objects and revoking access to any user as a dba, you must use the table 'DBA_TABLES' to generate the sql-statements. If you have revoked connect for a user, you can still see this user in the table 'DBA_USERS', but he has no more possibility to connect to the database. You cannot delete this entry completely (this is a good thing for history !) Servus from Germany Hermann Thoene, Muenchen, Bavaria <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< hthoene@doitcr.doit.sub.org