查询oracle数据库进程锁

双4562

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;

　　
　　
　　
　　感谢原创。。。。