Re: drop user xyz cascade sooo sloww...

From: Bob T <bob.mailist_at_gmail.com>
Date: 3 Oct 2005 19:32:50 -0700
Message-ID: <1128393170.464339.157300@o13g2000cwo.googlegroups.com>

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. Received on Mon Oct 03 2005 - 21:32:50 CDT