Very Computer

Hi,

Can anyone tell me how to put a large number of users into Oracle.  I
have a text file list of users and I need to generate a script to add
them to Oracle.  Can this be done with PL/SQL?  How?  Or does anyone
have a script for this that they have already written.

You can reply to my email or the newsgroup.

Geraldine Grubb

812 855-5492

Hi,

Can anyone tell me how to put a large number of users into Oracle.  I
have a text file list of users and I need to generate a script to add
them to Oracle.  Can this be done with PL/SQL?  How?  Or does anyone
have a script for this that they have already written.

You can reply to my email or the newsgroup.

Geraldine Grubb

812 855-5492

Hi,

Can anyone tell me how to put a large number of users into Oracle.  I
have a text file list of users and I need to generate a script to add
them to Oracle.  Can this be done with PL/SQL?  How?  Or does anyone
have a script for this that they have already written.

You can reply to my email or the newsgroup.

Geraldine Grubb

812 855-5492

Quick & Dirty:

If your file looks like

    <username> <password>

I guess it is not too difficult to have every line in your textfile
beginning with something like

    start adduser.sql <username> <password>

Save it as a SQL*Plus script. Next, write adduser.sql something like

-- adduser.sql
create user &1 identified by &2;

Ehhh, I don't know what the syntax to add a user is... Just start the*
script, which will call adduser for each line.

Arjan.

There are many ways to build a script to create these many users :
copy&paste in notepad, sed...
The great question is what to write in this script !
Why all these users, won't it be to heavy to manage them later ? A generic
user won't it be smarter ?
What actions are they supposed to do, what priviliges and ressources  will
they need ?.

I would use sqlldr to load the data into a table and issue a sql
statement against it to create another sql statement. Here's an
example assuming the table I loaded the users names into is called
temp_users

spo cre_users.sql

select 'create user ' || user_name ||' identified by password
default tablespace user_data temporary tablespace temporary_data
quota  unlimited on user_data;' from temp_users;

spo off

the output will look like this:
create user RPDATA identified by password
default tablespace user_data temporary tablespace temporary_data
quota  unlimited on user_data;

You could use this same idea to generate grants as well...

Good Luck,

Robert Prendin

#****************************************************
#       RP Data Solutions Inc.
#       Specializing in ORACLE DBA Support Services
#****************************************************

### start unix shell script
#
# you must be logged in as oracle to use this
#####
# syntax is:
#
#    whatever_you_name_it_script username password
#
#####
# change the grants to whatever you use
#####
# change the password for system from "manager" to whatever you use, or make
it a command-line parameter.
# or you might be able to use "connect internal", depending on your setup.
#####
#

echo "

connect system/manager

create user $1 identified by $2
        default tablespace your_default_tablespace
        temporary tablespace temp;

grant CREATE SESSION to $1;
grant CREATE TABLE to $1;
grant UNLIMITED TABLESPACE to $1;
grant CREATE PUBLIC SYNONYM to $1;
grant DROP PUBLIC SYNONYM to $1;

" | svrmgrl
#
#### end shell script