OraFAQ Forum: SQL & PL/SQL » how to drop all users with out knowing them?

Home » SQL & PL/SQL » SQL & PL/SQL » how to drop all users with out knowing them?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to drop all users with out knowing them? [message #6466]

Sat, 19 April 2003 00:25

Muhammad Waqas Shabir
Messages: 26
Registered: April 2003

Junior Member

I have dba user and want to drop all other users. What is that command.

Waiting anxiously!

Thankx!

Report message to a moderator

Re: how to drop all users with out knowing them? [message #6468 is a reply to message #6466]

Sat, 19 April 2003 02:46

Robert
Messages: 43
Registered: August 2000

Member

connect as user with dba privs.
set serveroutput on size 100000
begin
for i in (select username from dba_users where username not in ('SYSTEM','SYS') loop
execute immediate 'drop user '||i.username||' cascade';
dbms_output.put_line(i.username ||' dropped');
end loop;
end;

Report message to a moderator

Re: how to drop all users with out knowing them? [message #6469 is a reply to message #6466]

Sat, 19 April 2003 03:59

Frank
Messages: 7901
Registered: March 2000

Senior Member

Check this to see why this is NOT a good approach.
Make an explicit list of users you want to drop instead of just dropping every user.

hth
Frank

Report message to a moderator

Re: how to drop all users with out knowing them? [message #6473 is a reply to message #6466]

Sat, 19 April 2003 21:53

Anand
Messages: 161
Registered: August 1999

Senior Member

This is VERY, VERY bad idea!!

Are SYSTEM and SYS the only 'critical' users??

There are other users too depending on what all modules are installed in the database like DBSNMP, OUTLN, REPCAT (incase of Replication!) and an array of users. Just dropping all the users with a routine is not good.

Best way is to prepare a list of users and delete them one-by-one. A good DBA always checks thrice before doing anything. Your requirement is not the sign of a 'good' DBA.

Report message to a moderator

Re: how to drop all users with out knowing them? [message #6474 is a reply to message #6473]

Sun, 20 April 2003 02:35

Robert
Messages: 43
Registered: August 2000

Member

You only learn white mistakes?
The question was how to drop a user and not is it smart to do this

Report message to a moderator

Re: how to drop all users with out knowing them? [message #6475 is a reply to message #6473]

Sun, 20 April 2003 04:37

Frank
Messages: 7901
Registered: March 2000

Senior Member

It is obvious that this is not smart to do. If you don't know how to drop your users, you are bound to drop the wrong ones, ending up with an unusable database.

Frank

Report message to a moderator

Previous Topic:	How can I execute SQL script in PL/SQL procedure
Next Topic:	Oracle tutoial ( HELP NEEDED)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 05:58:35 CDT 2024