Path: utzoo!utgpu!news-server.csri.toronto.edu!bonnie.concordia.ca!ccu.umanitoba.ca!herald.usask.ca!alberta!ubc-cs!uw-beaver!milton!dali.cs.montana.edu!uakari.primate.wisc.edu!samsung!uunet!fernwood!oracle!news
From: mmorris@oracle.com (Martin Morris)
Newsgroups: comp.databases
Subject: Re: ORACLE question
Keywords: first post!
Message-ID: <1991Feb14.224703.8795@oracle.com>
Date: 14 Feb 91 22:47:03 GMT
References: <1991Feb14.163452.28616@infonode.ingr.com>
Reply-To: mmorris@oracle.UUCP (Martin Morris)
Distribution: comp
Organization: Oracle Corporation, Belmont, CA
Lines: 53

In article <1991Feb14.163452.28616@infonode.ingr.com> dalmandm@infonode.ingr.com (Dianne M. Dalman) writes:
>(Please excuse any glaring errors here, this is my first post!)
>
>Question:  In oracle, is there any "quick and dirty" way to delete a user,
>and drop all tables owned by that user?  I haven't been able to find anything
>in documentation about how to get rid of an oracle username once it's been
>created, and it's a hassle to have to go into the system tables to find
>out all the tables which have been created by a user, drop them one by one,
>and then just leave that "empty name" hanging out there.  Seems messy!
>
>(previously I used standard engine INFORMIX, where a simple "rm -r" solves
>all problems!)
>

The following SQL*Plus script will drop all objects belonging to a
user:

SET PAGES 999
SELECT 'DROP '||OBJECT_TYPE||' '||OBJECT_NAME||';'
FROM   USER_OBJECTS
WHERE  OBJECT_TYPE != 'INDEX'
SPOOL filename.sql
/
SPOOL OFF
@filename

Run this script as the user you wish to remove. After running it you
can then CONNECT to SYSTEM (or some other DBA account) and use
the REVOKE command to prevent access of the user.

Note that this is for a V6 database. For V5 use an equivilent SYSTEM
table (I thing the view TAB will do)

It is fairly easy to make this script (or similar) generic that just
accepts the username as a parameter and does it all. This is left as
an exercise to the reader... :-)

Martin

>
>---------------------------------------------------------------------
>Disclamer:  Opinions expressed herein are Strictly my own, and are
>	subject to change at my slightest whim, with no forewarning whatsoever
>	unless it's tuesday and the moon is full and my shoes are on 
>	backwards...i.e. Opinions are nearly as stable as a first release
>	of a brand new RDBMS based on previously untested relational theories...
>-----------------------------------------------------------------------
>Opinion, comments, answers >> uucp!ingr!wyle!dianne!dianne
>gripes, flames >>/dev/null

------------------------------------------------------------------------------
Martin Morris       SELECT STANDARD_DISCLAIMER    "Everybody is lost
Oracle UK           FROM   LEGAL_SPEAKS;           but me!" -- Indy