Path: utzoo!utgpu!news-server.csri.toronto.edu!bonnie.concordia.ca!uunet!wuarchive!zaphod.mps.ohio-state.edu!rpi!uupsi!sunic!dkuug!dde!Aragorn!ac
From: ac@dde.dk (Anders Conrad)
Newsgroups: comp.databases
Subject: Re: ORACLE question
Keywords: first post!
Message-ID: <1991Feb20.091040.14570@dde.dk>
Date: 20 Feb 91 09:10:40 GMT
References: <1991Feb14.163452.28616@infonode.ingr.com>
Distribution: comp
Organization: Dansk Data Elektronik A/S
Lines: 41

dalmandm@infonode.ingr.com (Dianne M. Dalman) writes:

>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!

I don't think you are able to delete the name from the user-list.

You can get rid of tables belonging to the user by running this
little script in SQL*Plus :

rem remtab.sql
rem DDE 1. August 1989.
rem removes all tables from the current user.
rem
set heading off
set termout off
set feedback off
set echo off
set pause off
set pagesize 1000
spool drop.sql
select 'drop '||tabtype||' '||tname||';' from tab;
spool off
start drop
set heading on
set termout on
set feedback on
set pause on
set echo on
select * from tab;
host rm drop.sql

--
Anders Conrad
Dansk Data Elektronik A/S
SQL Group
E-mail: ac@dde.dk or ..!mcsun!dkuug!dde!ac