Suppose if you wanted to create a series users like user1 to user100 with default tablespace is USER and temporary tablespace TEMP. I mean all the details for these users are same apart from the user name and password. In this scenario you can create the scripts using Microsoft Excel Concatenate function.
- Open a New excel spread sheet.
- In Column A populate the usernames from User1 to User100. You can use fill series option to generate n number of users.
- In Column B enter the following formula and copy the formula upto B100th column. This will generate the user creation scripts.
=CONCATENATE ("Create user ",A1," identified by ",A1," default tablespace users temporary tablespace temp;")
- In Column C enter the following formula and copy the formula upto C100th column which will generate the tablespace quota scripts.
=CONCATENATE ("ALTER USER ",A1," QUOTA UNLIMITED ON USERS;")
- In Column D enter the following formula and copy the formula upto D100th column which will generate the grant scripts.
=CONCATENATE ("grant connect, resource to ",A1,";")
- Copy the generated scripts into a .sql file and you can run it on SQL prompt.