Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: dropping all objects for an user
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 SQLprompt.
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