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: drop user xyz cascade sooo sloww...

Re: drop user xyz cascade sooo sloww...

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Wed, 05 Oct 2005 20:37:04 +0200
Message-ID: <di160m$egv$2@news6.zwoll1.ov.home.nl>


Bob T wrote:
> I found a way to do this that is much faster. I drop the tables with
> cascade and purge options. Here is a chunk of code from a perl script
> that does it:
>
> $outfile = 'new_user.tmp.sql';
> open(OUTFILE, "> $outfile")||die "Can't open $outfile $! \n";
> print OUTFILE "
> spool drop_tables.tmp.sql
> select 'drop table $new_user.'||lower(table_name)
> ||' cascade constraints purge;' from all_tables
> where owner = upper('$new_user');
> spool off
> start drop_tables.tmp.sql
> drop user $new_user cascade ;
> quit;
> ";
> close OUTFILE;
> $cmd = "sqlplus $system_login \@new_user.tmp.sql";
> system( $cmd);
>
> This creates and runs a script that contains all the tables for this
> user.
>

You can even speed that up by truncating the tables, before dropping them.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
Received on Wed Oct 05 2005 - 13:37:04 CDT

Original text of this message

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