Oracle kills user to build user password script

# ********************************# * Dba_oracle_awr.SH# ********************************# Usage:dba_oracle_awr.SH-s [instance_name]#-F [From Time]# -T [to Time]# -p [Report type, HTML or text]#-h [Oracle home]#-N [TNS admin]## TimeFormat'Yyyymmddhh24miss'. # e.g20110304170000Means to:xx: 00pm, MarGeneva, .### **********************# Get parameters#********************** whileGetopts": i:s:"opt Do Case$optinchi) instance=$OPTARG;; s) schemaName=$OPTARG;;'?')Echo "$0:invalid option-$OPTARG">&2Exit1;;Esac DoneOracle_home=$ORACLE _homeif["$instance"="" ] ThenEcho "Instance Name (-i) needed"Echo "Program exiting ..."Exit1fiif["$schemaName"="" ] ThenEcho "report_name name (-s} needed"Echo "Program exiting ..."Exit1fiSqlplus="${oracle_home}/bin/sqlplus"Echo$sqlplusEcho "start imp dmp--------------------------------------------"# *******************************# Get Begin and End snapshot id#*******************************Lock_user () {Echo "Lock User: $schemaName ....... ....."Sqlplus-s/nolog<<Eofconn/As sysdbaset term offalter USER $schemaName account LOCK; eof}restart_db () {Echo "Restart DB ... .... ....."Sqlplus-s/nolog<<Eofconn/As sysdbaset term offshutdown immediate;startup; Eof}create_kill_proc () {Echo "create kill Proc ....... ....."Sqlplus-s/nolog<<Eofconn/As sysdbacreate OR REPLACE PROCEDURE dba_kill_schemaname_session (schema_name VARCHAR2) VARCHAR2 ( +); Beginfor CUR in (SelectS.sid, S.serial#from v\ $session swhere username=schema_name) Loopdbms_output.put_line ('ALTER SYSTEM KILL SESSION" "|| CUR. SID | |','|| CUR. serial# | |" '"); s:='ALTER SYSTEM KILL SESSION" "|| CUR. SID | |','|| CUR. serial# | |" '"; EXECUTE IMMEDIATE S; END LOOP; END;/Eof}exec_kill_proc () {Echo "Kill Session ..... ......."Sqlplus-s/nolog<<Eofconn/As sysdbaexec dba_kill_schemaname_session ('$schemaName'); Eof}lock_user () {Echo "Lock User ....... ....."Sqlplus-s/nolog<<Eofconn/As sysdbaalter USER $schemaName account LOCK; Eof}unlock_user () {Echo "unlock user ... .... ....."Sqlplus-s/nolog<<Eofconn/As sysdbaalter USER $schemaName account UNLOCK; Eof}del_user () {Echo "drop User: $schemaName ....... ....."Sqlplus-s/nolog<<Eofconn/As sysdbaset term offdrop USER $schemaName CASCADE; eof}#*******************************# Generate AWR report#*******************************Create_user () {Echo "Create User: $schemaName ....... ....."Sqlplus-s/nolog<<Eofconn/As sysdbaset term offcreate USER $schemaNameIDENTIFIED by VALUES'$schemaName'DEFAULT tablespace tradetemporary tablespace tempprofile defaultaccount UNLOCK; GRANT CONNECT to $schemaName; GRANT imp_full_database to $schemaName; GRANT Plustrace to $schemaName; GRANT RESOURCE to $schemaName; ALTER USER $schemaName DEFAULT ROLE all; GRANT ADVISOR to $schemaName; GRANT UNLIMITED tablespace to $schemaName; GRANT EXECUTE on SYS. DBMS_FGA to $schemaName; GRANT READ, WRITE on DIRECTORY SYS. My_expdp_dump to $schemaName; eof}#*******************************# main routing#*******************************change_pwd () {Echo "The change passwd ...... ....."Sqlplus-s/nolog<<Eofconn/As sysdbaset term offalter user $schemaName identified by HOWBUY_QA_QWERVBNM; EOF} #restart_dblock_user #create_kill_procexec_kill_procdel_usercreate_userchange_pwd

Save As Imp.sh

Call

./imp.sh-i Orcl-s ${touser}_${database}

(You must capitalize because the database session name is all uppercase)

Oracle kills user to build user password script