Droping all user's from u database at a time

!hi
I tested these script on oracle9i DATABASE on winNT Platform. it works succesfully .


set timing on
set serveroutput on

create or replace procedure all_drop is

c_n integer;
string varchar2(100);

cursor cc is select username from dba_users where username not in ('SYS','SYSTEM');
x number:=0;
begin

for i in cc loop

string:='drop user '||i.username ||' cascade';
c_n :=dbms_Sql.open_cursor;

dbms_sql.parse(c_n,string,dbms_sql.native);

dbms_sql.close_cursor(c_n);
x:=x+1;
end loop;
dbms_output.put_line('Total No of user drop from u database :: '|| x);
end all_drop ;

Why didn't you use EXECUTE IMMEDIATE?

There are several 'users' created in 9i starter db that should NOT be deleted. a few examples: OUTLN, AURORA..., MDSYS, DBSNMP, etc.

A better shoice of a WHERE clause might be where default_tablespace<>'SYSTEM'

Why don't you try to drop the respective tablespaces instead.
I feel that's a more efficient way to deal ain't it?
DROP TABLESPACE INCLUDING CONTENTS;

I think dropping tablespaces does not drop users.
U have user a say YYY who has a default tablespace as say XXX.
Drop that tablespace using
Drop tablespace XXX including contents and datafiles;
drops the tablespace XXX but not the user YYY.


Kaushik