oracle殺使用者建使用者改密碼指令碼

標籤：set   echo   param   trace   passwd   main   loop   start   mis   

# ********************************# * 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]## time format: ‘yyyymmddhh24miss‘.# E.g 20110304170000 means 05:00:00pm, Mar 04, 2011### **********************# get parameters# **********************while getopts ":i:s:" optdocase $opt ini) instance=$OPTARG;;s) schemaName=$OPTARG;;‘?‘) echo "$0: invalid option -$OPTARG">&2exit 1;;esacdoneoracle_home=$ORACLE_HOMEif [ "$instance" = "" ]thenecho "instance name(-i) needed"echo "program exiting..."exit 1fiif [ "$schemaName" = "" ]thenecho "report_name name (-s} needed"echo "program exiting..."exit 1fisqlplus="${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) ASs VARCHAR2(1000);BEGINFOR CUR IN (select s.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 "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

 

儲存為imp.sh

調用

 ./imp.sh  -i ORCL -s ${toUser}_${dataBase}

（必須大寫，因為資料庫會話名稱都是大寫）

oracle殺使用者建使用者改密碼指令碼