环境介绍
- 在大量SELECT 活动会话堆积,导致CPU占用极高,可以使用下面语句进行批量关闭会话;
1 简洁 sql
BEGIN
FOR V_SESSID IN (SELECT SESS_ID FROM V$SESSIONS WHERE SQL_TEXT LIKE 'SELECT%')
LOOP
SP_CLOSE_SESSION(V_SESSID.SESS_ID);
END LOOP;
END;
2 完整sql
DECLARE
CURSOR C_SQL;
V_SESSID VARCHAR(32);
V_COUNT VARCHAR(8);
V_TEXT VARCHAR;
TYPE C_SESSION IS REF CURSOR RETURN V$SESSIONS%ROWTYPE;
V_SESSION C_SESSION;
V_WHERE VARCHAR(64) := 'WHERE SQL_TEXT LIKE ''SELECT%''';
BEGIN
OPEN C_SQL FOR 'SELECT COUNT(*) FROM V$SESSIONS ' || V_WHERE;
LOOP
FETCH C_SQL INTO V_COUNT;
EXIT WHEN C_SQL%NOTFOUND;
PRINT '总计:' || V_COUNT;
END LOOP;
CLOSE C_SQL;
OPEN C_SQL FOR 'SELECT SESS_ID FROM V$SESSIONS ' || V_WHERE;
LOOP
FETCH C_SQL INTO V_SESSID;
EXIT WHEN C_SQL%NOTFOUND;
PRINT V_SESSID;
SP_CLOSE_SESSION(V_SESSID);
END LOOP;
CLOSE C_SQL;
OPEN C_SQL FOR 'SELECT SESS_ID,SQL_TEXT FROM V$SESSIONS ' || V_WHERE;
IF C_SQL%FOUND THEN
LOOP
FETCH C_SQL INTO V_SESSID,V_TEXT;
EXIT WHEN C_SQL%NOTFOUND;
PRINT '未关闭的会话:';
PRINT 'SESS_ID: '||V_SESSID||', V_TEXT: '||V_TEXT;
PRINT '可以重复执行此操作';
END LOOP;
ELSE
PRINT '会话已经全部关闭';
END IF;
CLOSE C_SQL;
END;