oracle--常见只KILL 不活跃会话

 

 

 

SELECT l.sid,
       s.serial#,
       s.username,
       s.status,
       l.type AS lock_type,
       l.lmode AS mode_held,
       l.request AS mode_requested,
       o.object_name
FROM   v$lock l
JOIN   v$session s ON (l.sid = s.sid)
JOIN   dba_objects o ON (l.id1 = o.object_id)
WHERE  o.object_name = 'RETIRED_250221'
AND    o.object_type = 'TABLE';



SELECT sid, serial#, username, status 
FROM v$session 
WHERE username = USER;


ALTER SYSTEM KILL SESSION 'SID,SERIAL#';




###### 只想KILL当前用户的不活跃会话：

SET SERVEROUTPUT ON;

BEGIN
    FOR sess IN (
        SELECT sid, serial#
        FROM v$session
        WHERE username = USER
        AND status = 'INACTIVE'
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || sess.sid || ',' || sess.serial# || ''' IMMEDIATE';
        DBMS_OUTPUT.PUT_LINE('Killed active session: ' || sess.sid || ',' || sess.serial#);
    END LOOP;
END;
/