脚本-oracle个人用户并行限制

生产库个人用户大量使用并行会导致数据库负载忽高忽低,频繁分析,浪费时间,且可能会出现大量直接路径读,影响数据库整体性能(此问题跟他们讲了也不听,不胜其烦)
 
之前写过使用resource_managed进行并行的限制,该方法适用于数据库体量较小,连接和用户数量少的库,大体量的库会有latch产生(这里不过多阐述)
还有就是使用profile中的用户会话数量进行限制,这个会影响到业务人员开多个窗口的问题
无奈之下编写个人用户并行限制脚本
 
使用前提:
1.使用此药的数据库生产用户和个人用户有严格的权限划分,比如不同的profile
2.使用并行的用户会话会被kill掉,该用户被锁定5分钟后自动解锁
3.需要提前创建表 user_auto_lock,用户名请自定(稍微改下脚本即可)
 
#!/bin/bash
# File: auto_lock_parallel_user.sh
# Version: 1.0
#author jiacheng
#The concurrent user is locked and automatically unlocked after five minutes
##############################################################################
###########################---usage---########################################
#one node config!!!
#first: create table dbmt.user_auto_lock (exectime date default sysdate,sid number(10),serial number(7),inst_id number(3),username varchar2(100),sql_id varchar2(100),degree number(3),req_degree number(3),slaves number(3));
#linux: */1 7-20 * * * sh /home/oracle/scripts/auto_lock_parallel_user.sh >> /home/oracle/scripts/auto_lock_parallel_user.log
#HPUX: * 7-20 * * * sh /home/oracle/scripts/auto_lock_parallel_user.sh >> /home/oracle/scripts/auto_lock_parallel_user.log
###############################################################################




echo "The current working directory: $PWD"
echo "Shell: $0"

 

if [[ -f ~/.profile ]]
then
. ~/.profile
fi

 

if [[ -f ~/.bash_profile ]]
then
. ~/.bash_profile
fi

 

USERID=`/usr/bin/id -u -nr`
if [ $? -ne 0 ]
then
    echo "ERROR: unable to determine uid" 
    exit 99
fi

 

echo "current user: ${USERID}"

 

if [ "${USERID}" != "oracle" ]
then
   echo "ERROR: This script must be run as oracle"  
   exit 98
fi

 

if [ ! $ORACLE_SID ]
    then
           echo "Error: No ORACLE_SID set or provided as an argument"
           exit 97
else
   echo "current ORACLE_SID: ${ORACLE_SID}"
fi

 

STTIME=`date "+%Y-%m-%d %H:%M:%S"`
echo $STTIME



LOGFILE=./auto_lock_parallel_user.log

 

unset SQLPATH
unset ORACLE_PATH



unlock_user=`sqlplus -S '/ as sysdba' <<EOF >>$LOGFILE 
set serveroutput on;
begin
for r in (select username from dbmt.user_auto_lock where exectime<SYSDATE-5/1440 and exectime>SYSDATE-7/1440)
loop 
begin
    dbms_output.put_line('alter user ' || r.username || ' account unlock');
    EXECUTE IMMEDIATE 'alter user ' || r.username || ' account unlock';
exception
    when others then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
end;
end loop;
end;
/
exit;
EOF
`

 

echo "${unlock_user}"

 

function get_xinxi_sql
{
sqlplus -S '/ as sysdba' <<EOF
set pagesize 0 feedback off verify off heading off echo off  
set linesize 2000;
 select sid,serial,qcinst_id,username from (select pxs.qcsid sid,pxs.qcserial# serial,pxs.qcinst_id,ses.username,count(*) slaves FROM gv\$px_session pxs, gv\$session ses, gv\$px_process p,dba_users us WHERE ses.sid = pxs.sid AND ses.serial# = pxs.serial# AND p.sid = pxs.sid AND pxs.inst_id = ses.inst_id AND ses.inst_id = p.inst_id and ses.username=us.username AND pxs.req_degree IS NOT NULL and us.profile='JFZZZ' GROUP BY qcsid,qcserial#,pxs.qcinst_id,ses.username ORDER BY pxs.qcinst_id,slaves DESC);
insert into dbmt.user_auto_lock(exectime,sid,serial,inst_id,username,sql_id,degree,req_degree,slaves) select sysdate,pxs.qcsid sid,pxs.qcserial# serial,pxs.qcinst_id,ses.username,ses.sql_id,pxs.degree,pxs.req_degree,count(*) slaves FROM gv\$px_session pxs, gv\$session ses, gv\$px_process p,dba_users us WHERE ses.sid = pxs.sid AND ses.serial# = pxs.serial# AND p.sid = pxs.sid AND pxs.inst_id = ses.inst_id AND ses.inst_id = p.inst_id and ses.username=us.username AND pxs.req_degree IS NOT NULL and us.profile='JFZZZ' GROUP BY qcsid,qcserial#,pxs.qcinst_id,ses.username,ses.sql_id,pxs.degree,pxs.req_degree ORDER BY pxs.qcinst_id,slaves DESC;
commit;
exit;
EOF
}

 

px_xinxi=`get_xinxi_sql`
echo "${px_xinxi}"

 

if [ ! -n "$px_xinxi" ]
then
    echo "personal user no parallel session,exit"
    exit 88
else
    echo "discovery parallel session!!! next"
fi



echo "$px_xinxi" > current_parallel.log




kill_sql=`
while read s1 s2 s3 s4
do
echo "alter system kill session'$s1,$s2,@$s3' immediate;"
done < current_parallel.log`

 

echo "${kill_sql}" >> $LOGFILE

 

kill_parallel_session="sqlplus -S '/ as sysdba'
set sqlblanklines on
set pagesize 0  verify off heading off echo off 
"${kill_sql}"
exit;
"
echo "${kill_parallel_session}" | sh >> $LOGFILE

 

if [ $? -eq 0 ] 
then
   echo "personal user parallel killed"
else
   echo "$kill_parallel_session"
fi




lock_sql=`while read user
do
echo $user | awk '{print "alter user " $4 " account lock;"}'
done < current_parallel.log
`
echo "$lock_sql"

 

lock_user="sqlplus -S '/ as sysdba'
set pagesize 0  verify off heading off echo off 
"${lock_sql}"
exit;
"
echo "${lock_user}" | sh >> $LOGFILE



if [ $? -eq 0 ] 
then
   echo "parallel user locked"
else
   echo "ERROR"
fi



--编写不易,且用且珍惜,转载请注明来处

posted @ 2021-07-12 17:25  悠游~~~  阅读(216)  评论(0编辑  收藏  举报