ORACLE定期清理INACTIVE会话

简介:

   ORACLE数据库会话有ACTIVE、INACTIVE、KILLED、 CACHED、SNIPED五种状态。INACTIVE状态的会话表示此会话处于非活动、空闲、等待状态。例如PL/SQL Developer连接到数据库,执行一条SQL语句后,如果不继续执行SQL语句,那么此会话就处于INACTIVE状态。一般情况下,少量的 INACTVIE会话对数据库并没有什么影响,如果由于程序设计等某些原因导致数据库出现大量的会话长时间处于INACTIVE状态,那么将会导致大量的 系统资源被消耗,造成会话数超过系统session的最大值,出现ORA-00018:maximum number of sessions exceeded错误。

有时候需要清理那些长 时间处于INACTIVE状态的会话。人为定期检查、杀掉这类会话肯定不太现实,要定期清理那些长时间处于INACTIVE的会话,只能通过作业来实现; 另外需要注意,Kill掉这些会话需要需要谨慎,稍不注意,就有可能误杀了一些正常的会话。那么我们该如何定义这类会话呢?下面是我结合业务规则定义的:

  1: 会话的Status必须为INACTIVE,如果会话状态为ACTIVE、KILLED、CACHED、SNIPED状态,不做考虑。

  2: 会话必须已经长时间处于INACTIVE状态。例如,处于INACTIVE状态超过了两小时的会话进程,才考虑Kill。这个视具体业务或需求决定,有可 能超过半小时就可以杀掉会话进程。至于如何计算处于INACTIVE会话状态的时间,这个可以 通过V$SESSION的LAST_CALL_ET字段来判别,需要查询处于INACTIVE状态两小时或以上的会话,就可以通过查询条件 S.LAST_CALL_ET >= 60*60*2实现,当然最好写成 S.LAST_CALL_ET >= 7200

  3: 连接到会话的程序。比如,某个特定的应用程序产生的INACTIVE会话才要清理。例如, Toad工具、PL/SQL Developer工具。关于PROGRAM这个需要根据当前项目的具体情况设置,下面仅仅使用TOAD.EXE、W3WP.EXE举例说明。

clip_image002

   1: SELECT SID, SERIAL#,MODULE, STATUS
   2: FROM V$SESSION S
   3: WHERE S.USERNAME IS NOT NULL
   4: AND UPPER(S.PROGRAM) IN ('TOAD.EXE', 'W3WP.EXE')
   5: AND S.LAST_CALL_ET >= 60*60*2
   6: AND S.STATUS = 'INACTIVE'
   7: ORDER BY SID DESC;

如果是RAC环境,那么最好使用下面SQL语句,使用全局视图GV$SESSION。

   1: SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
   2: FROM gv$session S
   3: WHERE S.USERNAME IS NOT NULL
   4: AND UPPER(S.PROGRAM) IN ('TOAD.EXE', 'W3WP.EXE')
   5: AND S.LAST_CALL_ET >= 2 * 60*60
   6: AND S.STATUS = 'INACTIVE'
   7: ORDER BY INST_ID DESC

接下来创建存储过程SYS.DB_KILL_IDLE_CLIENTS. 方便调用该功能执行kill inactive 会话。注意:xxx部分用实际业务的PROGRAM来替代。

   1:  
   2: CREATE OR REPLACE PROCEDURE SYS.DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS
   3:    job_no number;
   4:    num_of_kills number := 0;
   5: BEGIN
   6:  
   7:    FOR REC IN
   8:        (SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
   9:         FROM gv$session S
  10:             WHERE S.USERNAME IS NOT NULL
  11:             AND UPPER(S.PROGRAM) IN ('xxx', 'xxx.EXE')   
  12:                  AND S.LAST_CALL_ET >= 2*60*60                              
  13:             AND S.STATUS= 'INACTIVE'
  14:         ORDER BY INST_ID ASC
  15:             ) LOOP
  16:          ---------------------------------------------------------------------------
  17:          -- kill inactive sessions immediately
  18:          ---------------------------------------------------------------------------
  19:          DBMS_OUTPUT.PUT('LOCAL SID ' || rec.sid || '(' || rec.module || ')');
  20:      execute immediate 'alter system kill session ''' || rec.sid || ', ' ||
  21:                             rec.serial# || '''immediate' ;
  22:      
  23:          DBMS_OUTPUT.PUT_LINE('. killed locally ' || job_no);
  24:          num_of_kills := num_of_kills + 1;
  25:    END LOOP;
  26:    DBMS_OUTPUT.PUT_LINE ('Number of killed xxxx system sessions: ' || num_of_kills);
  27: END DB_KILL_IDLE_CLIENTS;
  28: /

另外,由于kill session是直接将session kill掉,有可能出现导致事物回滚的现象,其实我们可以使用disconnect session完成当前事务并终止session。这种方式比alter system kill session跟安全可靠。

   1: CREATE OR REPLACE PROCEDURE SYS.DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS
   2:    job_no number;
   3:    num_of_kills number := 0;
   4: BEGIN
   5:  
   6:    FOR REC IN
   7:        (SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
   8:         FROM gv$session S
   9:             WHERE S.USERNAME IS NOT NULL
  10:             AND UPPER(S.PROGRAM) IN ('xxxx', 'xxxx')   
  11:                  AND S.LAST_CALL_ET >= 2*60*60                              
  12:             AND S.STATUS<>'KILLED'
  13:         ORDER BY INST_ID ASC
  14:             ) LOOP
  15:          ---------------------------------------------------------------------------
  16:          -- kill inactive sessions immediately
  17:          ---------------------------------------------------------------------------
  18:          DBMS_OUTPUT.PUT('LOCAL SID ' || rec.sid || '(' || rec.module || ')');
  19:          execute immediate 'alter system disconnect session ''' || rec.sid || ', ' ||
  20:                             rec.serial# || '''immediate' ;
  21:  
  22:          DBMS_OUTPUT.PUT_LINE('. killed locally ' || job_no);
  23:          num_of_kills := num_of_kills + 1;
  24:    END LOOP;
  25:    DBMS_OUTPUT.PUT_LINE ('Number of killed system sessions: ' || num_of_kills);
  26: END DB_KILL_IDLE_CLIENTS;
  27: /

然后,我们可以在作业(JOB)或Schedule里面定期调用该存储过程,也可以通过后台作业结合shell脚本实现定期清理空闲会话的功能。例如如下所示。

创建killSession.sh脚本,调用该存储过程SYS.DB_KILL_IDLE_CLIENTS

   1: #!/bin/bash
   2:  
   3:  
   4:  
   5: logfile=/home/oracle/cron/session/log/killSession.log
   6:  
   7: echo " " >> $logfile 2>&1
   8: echo "START ----`date`" >> $logfile 2>&1
   9: sqlplus /nolog <<STATS
  10: connect / as sysdba
  11: exec sys.db_kill_idle_clients;
  12: exit;
  13: STATS
  14:  
  15: echo "END ------`date`" >> $logfile 2>&1

在crontab里面配置后台作业,每隔15分钟运行一次,清理哪些满足条件的空闲会话。

0,15,30,45 * * * * /home/oracle/cron/session/bin/killSession.sh >/dev/null 2>&1

相关文章
|
Oracle 网络协议 关系型数据库
Oracle会话超时设置1:在sqlnet.ora和listener.ora中设置
这篇文章是Oracle会话超时设置的第一个文章,简述和Oracle 数据库 net services有关的会话超时管理。
1349 0
Oracle会话超时设置1:在sqlnet.ora和listener.ora中设置
|
3月前
|
SQL Oracle 关系型数据库
"揭秘!一键解锁Oracle日志清理魔法,让海量归档日志无处遁形,守护数据库健康,告别磁盘空间告急噩梦!"
【8月更文挑战第9天】随着Oracle数据库在企业应用中的普及,归档日志管理对保持数据库健康至关重要。归档日志记录所有更改,对数据恢复极为重要,但也可能迅速占用大量磁盘空间影响性能。利用Oracle提供的RMAN工具,可通过编写Shell脚本来自动清理归档日志。脚本包括设置环境变量、连接数据库、检查和删除指定时间前的日志,并记录执行情况。通过Cron作业定时运行脚本,可有效管理日志文件,确保数据库稳定运行。
86 7
|
4月前
|
SQL Oracle 关系型数据库
关系型数据库Oracle结束 RMAN 会话:
【7月更文挑战第25天】
69 1
|
4月前
|
SQL Oracle 关系型数据库
|
6月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的SET命令:你的数据库会话“调色板”
【4月更文挑战第19天】Oracle SQL*Plus的SET命令是数据库会话的“调色板”,用于设置输出格式、反馈信息和各种偏好。它能调整PAGESIZE和LINESIZE以优化显示,控制ECHO和FEEDBACK开关以定制反馈,以及统计命令执行时间(TIMING)和调试SQL(VERIFY)。更高级的选项如HEADING和COLSEP可改善输出的可读性。通过灵活运用SET命令,能提升工作效率和体验,是数据库管理员和开发者的必备工具。
|
6月前
|
SQL 监控 Oracle
oracle阻塞会话与kill
oracle阻塞会话与kill
|
6月前
|
SQL 存储 Oracle
oracle如何定期备份数据库sql文件
【1月更文挑战第7天】oracle如何定期备份数据库sql文件
103 8
|
6月前
|
SQL Oracle 关系型数据库
Oracle-使用切片删除的方式清理非分区表中的超巨数据
Oracle-使用切片删除的方式清理非分区表中的超巨数据
92 1
|
Oracle 关系型数据库
Oracle自带adrci工具清理告警日志
Oracle自带adrci工具清理告警日志
636 0
Oracle自带adrci工具清理告警日志
|
SQL Oracle 关系型数据库
Oracle_杂货箱_不定期更新
Oracle_杂货箱_不定期更新
45 0

推荐镜像

更多