Oracle Deadlock Condition

Tag: kill specifies the order line string code operation SID Str

ORACLE EBS operates on a form interface, or when a background database operates on a table and finds that it has been out of "suspended animation" state, possibly because the table is locked by one user, causing other users to be unable to continue the operation copy code code is as follows:

--锁表查询 SQL  SELECT object_name, machine, s.sid, s.serial#FROM gv$locked_object l, dba_objects o, gv$session sWHERE l.object_id　= o.object_idAND l.session_id = s.sid;

Locate the locked table and unlock the copy code code as follows:

--释放SESSION SQL:--alter system kill session ‘sid, serial#‘;ALTER system kill session ‘23, 1647‘;

ORA-00054: The resource is busy and requires the specified nowait

Question Review and reasons:

Error when creating index Ora-00054:resource busy and acquire with NOWAIT specified

Steps to resolve:

1：等待其他会话释放资源2：找出占用资源的会话，并删除 3：重启数据库

Principle Analysis:

1：创建索引时会产生的锁2：dml 语句会产生的锁3：索引创建时加上关键字 online时产生的锁

Identify the session that occupies the resource and remove

1: Find out all locked objects and locate which session occupies

select l.session_id,o.owner,o.object_namefrom v$locked_object l,dba_objects owhere l.object_id=o.object_id

Results:

session_id owner object_name158 SA TEST_1146 SA TEST_1131 SA TEST_3136 SA TEST_2

Locate which sessions need to be deleted by comparing the indexes you want to create

2: Find all the Locked sessions

select t2.username,t2.sid,t2.serial#,t2.logon_timefrom v$locked_object t1,v$session t2where t1.session_id=t2.sid order by t2.logon_time;

Results:

username  sid  158 15184 2014/12/4 14:55:59SA 146 8229 2014/12/4 15:23:22SA 136 14314 2014/12/4 16:09:59SA 131 54 2014/12/4 16:10:06

3:kill all resource-intensive sessions

Command form:

alter system kill session ‘sid,serial#‘;

Sessions that occupy Test_1 resources:

alter system kill session ‘158,15184‘;alter system kill session ‘146 ,8229‘;

Oracle unlocking, resolving "ora00054: Resource Busy" error

One. Process steps:

1. Get the session_id of the locked object

SELECT session_id FROM v$locked_object;

2. Get V$session's SID and serial# through session_id

SELECT sid, serial#, username, osuser FROM v$session where sid = session_id;

3. Terminate the relevant process.

ALTER SYSTEM KILL SESSION ‘sid,serial‘;

Two. Example:

SQL> select session_id from v$locked_object;SESSION_ID----------141

SQL> SELECT sid, serial#, username, osuser FROM v$session where sid = 141;SID SERIAL# USERNAME OSUSER---------- ---------- ------------------------------ ------------------------------141 41464 VC VC02\Admin

SQL> ALTER SYSTEM KILL SESSION ‘141,41464‘;System altered

SQL> select session_id from v$locked_object;SESSION_ID----------

Oracle Deadlock Condition