查询oracle数据库进程锁

DECLARE

    TYPE my_session_t IS TABLE OF v$SESSION%ROWTYPE;

    lock_others_session my_session_t;

    be_locked_session my_session_t;

    ls_output varchar2(255) :='';

    TYPE lock_others_t IS TABLE OF v$lock%ROWTYPE;

    lock_others lock_others_t;

    be_locked lock_others_t;

 BEGIN

    SELECT * BULK COLLECT INTO  lock_others FROM v$lock WHERE block=1;

    IF  lock_others.COUNT<1 THEN

        DBMS_OUTPUT.PUT_LINE('目前数据库中没有被其他进程锁住的进程!');

    ELSE

        DBMS_OUTPUT.PUT_LINE('目前数据库中共有' || TO_CHAR( lock_others.COUNT) || '个进程锁住别的进程!');  

        DBMS_OUTPUT.PUT_LINE('');

        FOR i IN 1 ..  lock_others.COUNT

        LOOP

            SELECT * BULK COLLECT INTO lock_others_session FROM v$session WHERE sid=lock_others(i).sid;

            DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------------------------------------------');

            DBMS_OUTPUT.PUT_LINE('第' || i || '个锁的进程信息:');

            ls_output := 'SID为' || lock_others_session(1).sid;

            ls_output := ls_output || '  ,登陆ORACLE帐号为 ' || lock_others_session(1).username;

            ls_output := ls_output || '  ,登陆操作系统帐号为 ' || lock_others_session(1).osuser;

            ls_output := ls_output || '  ,计算机名为 ' || lock_others_session(1).machine || ' ,';

            DBMS_OUTPUT.PUT_LINE( ls_output);

            ls_output := ' 正在进行的操作为 ' || TO_NCHAR(lock_others_session(1).action); --使用TO_NCHAR来防止出现ORA-29275异常

            ls_output := ls_output || ' 正在运行的模块为 ' || lock_others_session(1).module;

            DBMS_OUTPUT.PUT_LINE( ls_output);

            BEGIN

                SELECT sql_text INTO ls_output FROM v$sqltext 

                WHERE hashvalue=lock_others_session(1).sql_hash_value and piece=0;

                ls_output := '准备执行的SQL语句为 ' || ls_output; --使用TO_NCHAR来防止出现ORA-29275异常

                DBMS_OUTPUT.PUT_LINE( ls_output);

            EXCEPTION

                WHEN OTHERS THEN

                NULL;

            END;

            DBMS_OUTPUT.PUT_LINE('如果要杀掉该锁，请执行语句: ALTER SYSTEM KILL SESSION ''' 

            || lock_others_session(1).sid || ',' || lock_others_session(1).serial# || ''';');

            SELECT * BULK COLLECT INTO be_locked FROM v$lock WHERE id1=lock_others(i).id1 and id2= lock_others(i).id2 and request > 0;

            DBMS_OUTPUT.PUT_LINE('');

            DBMS_OUTPUT.PUT_LINE('锁住了如下' || TO_CHAR(be_locked.COUNT) || '个进程:');

            FOR j IN 1 .. be_locked.COUNT

            LOOP

                SELECT * BULK COLLECT INTO be_locked_session FROM v$session 

                WHERE sid=be_locked(j).sid;

                DBMS_OUTPUT.PUT_LINE('***********************************************');

                DBMS_OUTPUT.PUT_LINE('第' || j || '个被锁住的进程信息:');

                ls_output := 'SID为' || be_locked_session(1).sid;

                ls_output := ls_output || '  ,登陆ORACLE帐号为 ' || be_locked_session(1).username;

                ls_output := ls_output || '  ,登陆操作系统帐号为 ' || be_locked_session(1).osuser;

                ls_output := ls_output || '  ,计算机名为 ' || be_locked_session(1).machine || ' ,';

                DBMS_OUTPUT.PUT_LINE( ls_output);

                ls_output := ' 正在进行的操作为 ' || TO_NCHAR(be_locked_session(1).action) || ' ,'; --使用TO_NCHAR来防止出现ORA-29275异常

                ls_output := ls_output || ' 正在运行的模块为 ' || be_locked_session(1).module || ' ,';

                DBMS_OUTPUT.PUT_LINE( ls_output);               

                BEGIN

                    SELECT sql_text INTO ls_output FROM v$sqltext 

                    WHERE hashvalue=be_locked_session(1).sql_hash_value and piece=0;

                    ls_output := '正在执行的SQL语句为 ' || ls_output; --使用TO_NCHAR来防止出现ORA-29275异常

                    DBMS_OUTPUT.PUT_LINE( ls_output);

                EXCEPTION

                    WHEN OTHERS THEN

                        NULL;

                END;

            END LOOP;

            DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------------------------------------------');

            DBMS_OUTPUT.PUT_LINE('');

        END LOOP;

    END IF;

EXCEPTION

    WHEN OTHERS THEN --通用显示错误信息

        DBMS_OUTPUT.PUT_LINE(SQLERRM ((-1) * SQLCODE));

END;

 

 

 

感谢原创。。。。