# ********************************
# * 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:" opt
do
case $opt in
i) instance=$OPTARG
;;
s) schemaName=$OPTARG
;;
‘?‘) echo "$0: invalid option -$OPTARG">&2
exit 1
;;
esac
done

oracle_home=$ORACLE_HOME
if [ "$instance" = "" ]
then
echo "instance name(-i) needed"
echo "program exiting..."
exit 1
fi
if [ "$schemaName" = "" ]
then
echo "report_name name (-s} needed"
echo "program exiting..."
exit 1
fi

sqlplus="${oracle_home}/bin/sqlplus"
echo $sqlplus
echo "start imp dmp--------------------------------------------"

# *******************************
# get begin and end snapshot ID
# *******************************
lock_user()
{
echo "lock user: $schemaName ................"

sqlplus -S /nolog<<EOF
conn / as sysdba
set term off
ALTER USER $schemaName ACCOUNT LOCK;
EOF
}

restart_db()
{
echo "restart DB ................"

sqlplus -S /nolog<<EOF
conn / as sysdba
set term off
shutdown immediate;
startup;
EOF
}


create_kill_proc()
{
echo "create kill proc ................"
sqlplus -S /nolog<<EOF
conn / as sysdba
CREATE OR REPLACE PROCEDURE DBA_KILL_SCHEMANAME_SESSION(SCHEMA_NAME VARCHAR2) AS
s VARCHAR2(1000);
BEGIN
FOR CUR IN (select s.SID, s.SERIAL#
from v\$session s
where username = SCHEMA_NAME) LOOP
dbms_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<<EOF
conn / as sysdba
exec DBA_KILL_SCHEMANAME_SESSION(‘$schemaName‘);
EOF
}


lock_user()
{
echo "lock user ................"

sqlplus -S /nolog<<EOF
conn / as sysdba
ALTER USER $schemaName ACCOUNT LOCK;
EOF
}

unlock_user()
{
echo "unlock user ................"

sqlplus -S /nolog<<EOF
conn / as sysdba
ALTER USER $schemaName ACCOUNT UNLOCK;
EOF
}


del_user()
{
echo "drop user: $schemaName ................"

sqlplus -S /nolog<<EOF
conn / as sysdba
set term off
DROP USER $schemaName CASCADE;
EOF
}
# *******************************
# generate AWR report
# *******************************
create_user()
{
echo "create user: $schemaName ................"

sqlplus -S /nolog<<EOF
conn / as sysdba
set term off
CREATE USER $schemaName
IDENTIFIED BY VALUES ‘$schemaName‘
DEFAULT TABLESPACE TRADE
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT 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<<EOF
conn / as sysdba
set term off
alter user $schemaName identified by howbuy_qa_qwerVBNM;
EOF
}


#restart_db

lock_user
#create_kill_proc
exec_kill_proc
del_user
create_user
change_pwd