问题描述
业务人员在insert时发生报错
原理分析
1.创建索引时会产生的锁
2.dml 语句会产生的锁
3.索引创建时加上关键字 online时产生的锁
解决步骤
1.等待其他会话释放资源
2.找出占用资源的会话,并删除
3.重启数据库
解决:
SQL> select session_id from v$locked_object;
SQL> SELECT sid, serial#, username, osuser FROM v$session where sid = xx;
SQL> ALTER SYSTEM KILL SESSION 'xx,xxx';
查看被锁的表和引起锁表的session:
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;
SQL> select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;
SQL> select xidusn,object_id,session_id, oracle_username,os_user_name from v$locked_object ;
SQL> select object_id,object_name from dba_objects where object_id='145943'; # 第三步查到的object id
SQL> alter system kill session '25,1' ; # 第一步查到的session id和第二步查到的serial#