Path: utzoo!attcan!uunet!spool2.mu.edu!samsung!usc!snorkelwacker.mit.edu!ira.uka.de!smurf!subnet.sub.net!altger!doitcr!hthoene
From: hthoene@doitcr.doit.sub.org (Hermann Thoene)
Newsgroups: comp.databases
Subject: Re: Deleting Oracle users
Message-ID: <1564@doitcr.doit.sub.org>
Date: 12 Jan 91 12:21:53 GMT
References: <1991Jan8.204156.7959@engin.umich.edu>
Organization: DOITCR / FLOENZ1, Public Access Unix Munich/Germany
Lines: 34

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