Mar 2, 2010

creating user using shell script on particular server

Scenario : Need to create a shell script to create user in the database which is running on that following server.

Script1 : dbcreate.sh

#!/bin/ksh
#script name dbcreate.sh

for SID in `cat /etc/oratab | grep -v "#"| grep ":"| awk -F: '{print $1}'`
do
#export ORACLE_SID=${SID}
. setdb ${SID} auto #env variable
sqlplus /nolog <
connect / as sysdba;
@usercreation.sql;
EOF
done

Script 2 : users.sql

SET ECHO OFF;
SET NEWPAGE 0;
SET SPACE 0;
SET PAGESIZE 0;
SET FEEDBACK OFF;
SET TRIMSPOOL ON;
SET TAB OFF;
SET LINESIZE 300;
CREATE USER IDENTIFIED BY
ACCOUNT LOCK;
GRANT DBA TO ;
spool on;
select name from v$database;
select username,account_status from dba_users where username in 'xxx';
spool off;
host cat on.lst >> created_user_list.lst
host rm on.lst
exit;