Oracle Kill oracle dead-lock session without DBA role

Oracle Kill oracle dead-lock session without DBA role

--查  Oracle 目前所有 LOCK_OBJECT

SELECT * FROM TABLE(QUERY_LOCK_OBJECT);

--強制刪除當前連線使用者有 LOCK_OBJECT 之 SESSION (帶入數字)
BEGIN
    KILL_SESSION(sid, serial#);
END;

DBA_query_lock_object.sql

CREATE OR REPLACE FUNCTION DBA.query_lock_object
    RETURN LOCK_OBJECT_TABLE
    IS
    CURSOR c_lock IS SELECT t2.USERNAME,
                            t2.SID,
                            t2.SERIAL#,
                            t3.OBJECT_NAME,
                            t2.OSUSER,
                            t2.MACHINE,
                            t2.PROGRAM,
                            t2.LOGON_TIME,
                            t2.COMMAND,
                            t2.LOCKWAIT,
                            t2.SADDR,
                            t2.PADDR,
                            t2.TADDR,
                            t2.SQL_ADDRESS,
                            t1.LOCKED_MODE
                     FROM SYS.V_$LOCKED_OBJECT t1,
                          SYS.V_$SESSION t2,
                          SYS.DBA_OBJECTS t3
--                      WHERE USERNAME = SYS_CONTEXT('USERENV', 'SESSION_USER')
--                        AND t1.SESSION_ID = t2.SID
                     WHERE t1.SESSION_ID = t2.SID
                       AND t1.OBJECT_ID = t3.OBJECT_ID
                     ORDER BY t2.LOGON_TIME;

    o_data   LOCK_OBJECT;
    t_result LOCK_OBJECT_TABLE;
BEGIN
    FOR r_lock IN c_lock
        LOOP
            o_data := LOCK_OBJECT(
                    r_lock.USERNAME,
                    r_lock.SID,
                    r_lock.SERIAL#,
                    r_lock.OBJECT_NAME,
                    r_lock.OSUSER,
                    r_lock.MACHINE,
                    r_lock.PROGRAM,
                    r_lock.LOGON_TIME,
                    r_lock.COMMAND,
                    r_lock.LOCKWAIT,
                    r_lock.SADDR,
                    r_lock.PADDR,
                    r_lock.TADDR,
                    r_lock.SQL_ADDRESS,
                    r_lock.LOCKED_MODE
                );

            t_result := LOCK_OBJECT_TABLE();
            t_result.EXTEND;
            t_result(t_result.COUNT) := o_data;
        END LOOP;

    RETURN t_result;
END query_lock_object;

DBA_kill_session.sql

CREATE OR REPLACE PROCEDURE DBA.kill_session(p_sid NUMBER, p_serial NUMBER)
    AUTHID DEFINER IS
    l_session_count    PLS_INTEGER;
    l_locked_obj_count PLS_INTEGER;
    l_exec_user        VARCHAR2(30);
    l_is_admin         BOOLEAN := FALSE;
BEGIN
    SELECT UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER')) INTO l_exec_user FROM DUAL;
    l_is_admin := l_exec_user IN ('SYS', 'SYSTEM', 'DBA');
--     DBMS_OUTPUT.PUT_LINE(SYS.DIUTIL.BOOL_TO_INT(l_is_admin));

    IF l_is_admin THEN
        SELECT COUNT(1)
        INTO l_session_count
        FROM SYS.V_$SESSION
        WHERE SID = p_sid
          AND SERIAL# = p_serial;
    ELSE
        SELECT COUNT(1)
        INTO l_session_count
        FROM SYS.V_$SESSION
        WHERE USERNAME = l_exec_user
          AND SID = p_sid
          AND SERIAL# = p_serial;
    END IF;

    IF l_session_count = 1 THEN
        SELECT COUNT(1)
        INTO l_locked_obj_count
        FROM SYS.V_$LOCKED_OBJECT t1,
             SYS.V_$SESSION t2
        WHERE t1.SESSION_ID = t2.SID
          AND SID = p_sid
          AND SERIAL# = p_serial;

        IF l_locked_obj_count > 0 THEN
            EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';
        ELSIF l_is_admin THEN
            RAISE_APPLICATION_ERROR(-20004,
                                    'Blocked kill session attempt, there is no locked object with session (sid=['
                                        || p_sid || '] serial#=[' || p_serial || '])');
        ELSE
            RAISE_APPLICATION_ERROR(-20003,
                                    'Blocked kill session attempt, there is no locked object with session (owner=['
                                        || l_exec_user || '] sid=[' || p_sid || '] serial#=[' || p_serial || '])');
        END IF;

    ELSIF l_is_admin THEN
        RAISE_APPLICATION_ERROR(-20003,
                                'Blocked kill session attempt, there is no session with given condition (sid=['
                                    || p_sid || '] serial#=[' || p_serial || '])');
    ELSE
        RAISE_APPLICATION_ERROR(-20002,
                                'Blocked kill session attempt, there is no session with given condition (owner=['
                                    || l_exec_user || '] sid=[' || p_sid || '] serial#=[' || p_serial || '])');
    END IF;

END kill_session;

DBA_LOCK_OBJECT.sql

create or replace TYPE LOCK_OBJECT IS OBJECT (
    USERNAME    VARCHAR2(128),
    SID         NUMBER,
    SERIAL#     NUMBER,
    OBJECT_NAME VARCHAR2(128),
    OSUSER      VARCHAR2(128),
    MACHINE     VARCHAR2(64),
    PROGRAM     VARCHAR2(48),
    LOGON_TIME  DATE,
    COMMAND     NUMBER,
    LOCKWAIT    VARCHAR2(16),
    SADDR       RAW(8),
    PADDR       RAW(8),
    TADDR       VARCHAR2(16),
    SQL_ADDRESS RAW(8),
    LOCKED_MODE NUMBER
);

create or replace TYPE LOCK_OBJECT_TABLE IS TABLE OF LOCK_OBJECT;