remove a large number of user from oracle

Shell Programming and Scripting
1

Hi

on solaris and oracle 10g2, I have number of users created in Oracle, I wonder if I have a list of the usernames will it be possible to remove the users quickly ?

I want to keep the users access to system but oracle.

some thing like shell script may be ?:confused:

I am trying to think of a way by which I can just feed the list to that script and script either execute like you said or one by one..

I don't know how to use shell script to connect to oracle and execute queries then...

Thanks

2

I'm sorry, I don't quite understand what you are trying to do.

Are you trying to remove a list of users from Oracle? Or from the system?

You can perform a simple SQL command in Oracle to delete the users from a table. I take it that's what you're trying to get a shell script to do?

Check out this link: INTRODUCTION TO ORACLE
Oracle DELETE Statement

DELETE from [table name] where [field name] = 'whatever'; is basically what you need to do if deleting from a table in Oracle.

3

Try this, I am not sure whether it works or not as i don't have oracle database.

#! /usr/bin/ksh
sqlplus user/pwd@database<<EOF
! for usr in `cat usernames.txt`
! do
drop user $usr;
!done
EOF

Or else you can try

#! /usr/bin/ksh
for usr in `cat usernames.txt`
do
sqlplus user/pwd@database<<EOF
drop user $usr;
EOF
done

4

Thanks :b: :slight_smile:

5

Do you also happen to know,

if I have a user in oracle configured with some privileges, can I add many users and have same privilege like the already configured user ?

currently,

I use web interface and use CREATE LIKE but it is time consuming to add users and set passwords through web because involves lot of copy paste in it and manual work for each new user.

Any ideas ?:confused:

Thanks