1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
| 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 hash_value = 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 hash_value = 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;
|