DM清理kill大于10s的select会话语句

使用过程定期清理kill大于10s的select会话语句，以免阻塞其它会话，供参考

/******************************************
功能说明：定期清理kill大于10s的select会话语句，以免阻塞其它会话
CREATE TABLE "KILL_STL_SESS_BAK"
(
"TF" VARCHAR2(39),
"SESS_ID" BIGINT,
"TRX_ID" BIGINT,
"MSGT" INTEGER,
"SQL_TEXT" VARCHAR2(32767),
"CURR_SCH" VARCHAR(128),
"USER_NAME" VARCHAR(128),
"CLNT_HOST" VARCHAR(128),
"CLNT_IP" VARCHAR(128),
"CLNT_TYPE" VARCHAR(128),
"OSNAME" VARCHAR(128),
"LAST_SEND_TIME" VARCHAR2(32767),
"RECORDING_TIME" TIMESTAMP
);

create or replace procedure dm_dba_close_slt_session 
    AUTHID DEFINER
as
begin
    for rs in( select 'sp_close_session('||TF||');' as "op_sql",
            "SESS_ID",
            "TRX_ID" ,
            "MSGT" ,
            "SQL_TEXT" ,
            "CURR_SCH" ,
            "USER_NAME",
            "CLNT_HOST" ,
            "CLNT_IP" ,
            "CLNT_TYPE" ,
            "OSNAME" ,
            "LAST_SEND_TIME",
            getdate() as "RECORDING_TIME"
       from ( select sess_id tf,-- sp_close_session sess_id                                   ,
                    sess_id ,
                    trx_Id ,
                    datediff(ss, last_recv_time, sysdate) MsgT,                             --已执行时间 s
                    '--'||CURR_SCH ||' '|| to_char(sf_get_session_sql(sess_id)) "SQL_TEXT" ,--完整sql
                    curr_sch ,
                    user_name ,
                    clnt_host ,
                    clnt_ip ,
                    clnt_type ,
                    osname ,
                    left(last_send_time, 19) as last_send_time
               from v$sessions
              where state='ACTIVE'
                    -- 过滤update类型的SQL
                and lower (to_char(sf_get_session_sql(sess_id))) like 'select %' )
      where MsgT>=10 )  --MsgT 单位秒，查询大于10s的select会话语句;
    loop
        execute immediate rs."op_sql";
        insert into "KILL_SESS_BAK" values(rs."op_sql", 
               rs."SESS_ID", 
               rs."TRX_ID", 
               rs."MSGT", 
               rs."SQL_TEXT", 
               rs."CURR_SCH", 
               rs."USER_NAME", 
               rs."CLNT_HOST",
               rs."CLNT_IP", 
               rs."CLNT_TYPE", 
               rs."OSNAME", 
               rs."LAST_SEND_TIME", 
               rs."RECORDING_TIME");
         
        commit;
    end loop;
end;
/ --disql执行需要加/

--执行语句kill会话
dm_dba_close_slt_session;

查询备份表结果示例：

select * from KILL_SESS_BAK;