出现锁的根本解决办法不是去手动解锁啊,
而是应该去看,是哪个SESSION,哪个sql语句锁的,锁了哪些资源,是不是应该锁定这些资源！
如果有些锁是不必要的,要么commit事务来释放锁,要不就不锁定这些资源！
修改应用才是应该做的！
Session1创建测试表：
Table created.
SQL desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
SQL insert into test values(001,'tom');
①. row created.
查看test表信息
SQL update test set name='xue' where name='joy';
①. row updated.
SQL commit;
Commit complete.
SQL select * from test updata;
ID NAME
---------- --------------------
①. tom
SQL select * from test；
update模拟锁表
SQL update test set name='da' where name='tom';
注：不提交
查看哪个表被锁
SQL select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SESSION_ID LOCKED_MODE
---------- -----------
SYS
TEST
查看是哪个session引起的
SQL select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
USERNAME SID SERIAL# LOGON_TIM
------------------------------ ---------- ---------- ---------
杀掉对应进程
System altered.
no rows selected
session 1查询：
SQL select * from test;
select * from test
*
ERROR at line 1:
重新连接SQL
Connected to:
With the Partitioning, OLAP, Data Mining and Real Application Testing options
①.、在做Oracle监听程序测试时,发现帐户已经被锁定.
① 先通过top命令查看产用资源较多的spid号
select sid,serial#,machine,username,program,sql_hash_value,sql_id,
select sql_text
from v$sqltext_with_newlines
where hash_value = hash_value
order by piece;
也可直接使用:
select a.*,b.SQL_TEXT from (
) a,v$sql b
where a.sql_id? = b.SQL_ID(◆)
Alter system kill session 'SID,SERIAL#'
解锁：
①查询哪些对象被锁：
? select object_name,machine,s.sid,s.serial#
? from v$locked_object l,dba_objects o ,v$session s
? where l.object_id = o.object_id and l.session_id=s.sid;
? select spid, osuser, s.program
? from v$session s,v$process p
首先你要知道表锁住了是不是正常锁?因为任何DML语句都会对表加锁.
你要先查一下是那个会话那个sql锁住了表,有可能这是正常业务需求,不建议随便KILL session,如果这个锁表是正常业务你把session kill掉了会影响业务的.
建议先查原因再做决定.
(1)锁表查询的代码有以下的形式：
select count(*) from v$locked_object;
select * from v$locked_object;
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
select b.username,b.sid,b.serial#,c.* from v$locked_object a,v$session b,v$sql c where a.session_id = b.sid
and b.SQL_ID = c.sql_id and c.sql_id = ''
order by b.logon_time;
土嘎嘎的粉丝们大家好：这个死锁没办法完全避免,尽量的话在做事物提交的时候,提交完成后在进行其余的同一个表的操作,再就是insert、update等操作尽量能减少就减少.其实正常情况下是很少出现死锁的.
以上就是土嘎嘎小编为大家整理的如何减少oracle锁表相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！