今天本来要修改测试库程序版本的,发现被卡住了。一直无法完成,后来发现是表被锁死了。然后通过网上网友们帖子的帮助,成功的解决了问题。这里记录一下:
1.首先查看当前数据库中,哪些表被锁住了,找到对应的Session_id
执行此语句:
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: 会话ID
LOCKED_MODE: 锁级别
锁级别分为6级:
1级锁有:Select
2级锁有:Select for update,Lock For Update,Lock Row Share
3级锁有:Insert, Update, Delete, Lock Row Exclusive
4级锁有:Create Index, Lock Share
5级锁有:Lock Share Row Exclusive
6级锁有:Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
2.执行该语句,查看会话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;
执行结果:
通过刚才找到的Session_id找到次查询结果的SID
3.杀会话
alter system kill session 'sid,serial#';
例子:
alter system kill session '20,30153'; -----这个是我刚才杀死会话前的执行语句
如果有ora-00031错误,则在后面加immediate;
alter system kill session '20,30153' immediate;
注:注意确定好会话id