-
!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 ;
-
EXECUTE IMMEDIATE ????
Why didn't you use EXECUTE IMMEDIATE?
Michael T Hudacko
Senior Oracle DBA
-
A better WHERE clause
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'
vaidis
Senior Oracle DBA
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|