Oracle 解锁 的sql (product 慢可以用一下)
来源:互联网 发布:javascript编写 编辑:程序博客网 时间:2024/04/29 20:17
发现oracle 占用了很多cpu,可能是sql 锁,下面发现的sql 就可以试试用来解锁:
set serveroutput on size 10000
declare
cursor c1 is
select * from v$lock where request != 0
order by id1, id2;
wid1 number := -999999;
wid2 number := -999999;
wholder_detail varchar2(200);
v_err_msg varchar2(80);
wsid number(5);
wstep number(2);
wtype varchar2(10);
wobject_name varchar2(180);
wobject_name1 varchar2(80);
wlock_type varchar2(50);
w_lastcallet varchar2(11);
h_lastcallet varchar2(11);
begin
for c1_rec in c1 loop
if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
null;
else
wstep := 10;
select sid , type into wsid , wtype
from v$lock
where id1 = c1_rec.id1
and id2 = c1_rec.id2
and request = 0
and lmode != 4;
dbms_output.put_line(' ');
wstep := 20;
select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= wsid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
begin
select decode(wtype,'TX', 'Transaction',
'DL', 'DDL Lock',
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Un-Known Type of Lock')
into wlock_type
from dual;
declare
cursor c3 is
select object_id from v$locked_object
where session_id = wsid;
begin
wobject_name := '';
for c3_rec in c3 loop
select object_type||': '||owner||'.'||object_name
into wobject_name
from dba_objects
where object_id = c3_rec.object_id;
wobject_name := wobject_name ||' '||wobject_name1;
end loop;
exception
when others then
wobject_name := wobject_name ||' No Object Found';
end;
dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
exception
when no_data_found then
dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
end;
end if;
wstep := 30;
select '.... Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= c1_rec.sid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
wid1 := c1_rec.id1;
wid2 := c1_rec.id2;
end loop;
if wid1 = -999999 then
wstep := 40;
dbms_output.put_line('No one requesting locks held by others');
end if;
exception
when others then
v_err_msg := (sqlerrm ||' '|| sqlcode||' step='||to_char(wstep));
DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;
/
其实,放到一个指定路径下 sqlplus / as sysdba 后,@sql 就可以执行了。
- Oracle 解锁 的sql (product 慢可以用一下)
- Oracle解锁的SQL
- oracle解锁的SQL....
- Oracle 查询每天执行慢的SQL
- 查询Oracle正在执行的sql语句,锁表,解锁
- Oracle的解锁
- Oracle解锁的方式
- Oracle-表的解锁
- 数据库解锁的sql
- oracle 经常用的函数,没用过的可以学一下
- 有时间可以看看复习一下的oracle(转)
- oracle数据源的报表sql计算慢&解决
- oracle表查询速度极慢的处理过程记录一下
- oracle用户被锁-解锁SQL
- Oracle锁表查看和解锁sql
- oracle查询锁表,并解锁sql
- 博客慢的真是可以
- oracle 锁表、解锁的语句
- Android企业应用项目开发总结
- 算法——概率生成器
- python3之python的核心数据类型(文件)
- 一步一个脚印学习WCF之一WCF概要(下)之生成元数据与代理
- imap send email by python
- Oracle 解锁 的sql (product 慢可以用一下)
- C语言初级学习
- 游戏数据参数
- 转载的,人生的故事
- C#获取usb插入消息
- basic_string::c_str() 与 basic_string::data() 区别
- 深入浅出之正则表达式
- Jackson方法的小例子
- 一步一步写算法(之图的保存)