Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: dropping all objects for an user

Re: dropping all objects for an user

From: <sanjay.raj_at_roche.com>
Date: Thu, 22 Apr 1999 19:38:19 GMT
Message-ID: <7fntrb$26q$1@nnrp1.dejanews.com>


In article <7fkgru$her$1_at_demon.uunet.ca>,   "dfp1" <dfp1_at_istar.ca> wrote:
> Hi,
>
> Is there a SQL statement that will let me drop all the objects in an users
> schema, but preserve the user (with privileges etc. intact)?
>
> Thanks,
> Dinesh
>
>

This is the script that I use

set feedback off
set heading off
set verify off
set pagesize 1000
set linesize 70
accept ORA_SID char format 'A20'-
prompt 'Which Instance you want to drop tables from : ' accept USER char format 'A20'-
prompt 'Which user`s schema you want to drop tables from : ' connect &user/&user@&ORA_SID
spool drop
select 'drop table '||tname||' cascade constraints;'from tab;

-- where tname like 'SM%';
-- ## The following is comented because at times U may want to drop all but 5
-- ## say) tables. In that case edit drop.lst and then run drop.lst at SQL
prompt.
--start drop.lst
exit;
/

hope this helps

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 22 1999 - 14:38:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US