When we operate the database, sometimes the database table is locked due to improper operation, so we often do not know what it is, I don't know how to unlock these tables, "sessions" inside "Tools" in the PL / SQL Developer tool You can query the currently existing sessions, but it's hard to find that session is locked. If you want to find it, you are more difficult. The following is called the query statement to query the session that is locked. as follows:
Select sn.username, m.sid, sn.serial #, m.type, decode (M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'ROW EXCL.' , 4, 'Share', 5, 'S / ROW EXCL.', 6, 'Exclusive', LMODE, LTRIM (To_Char (LMODE, '990'))) LMODE, DECODE (M.Request, 0, 'None' , 1, 'Null', 3, 'ROW EXCL.', 4, 'Share', 5, 'S / ROW EXCL.', 6, 'Exclusive', Request, Ltrim (TO_CHAR M.Request, '990'))) Request, M.ID1, M.ID2 from V $ Session Sn, V $ LOCK M where (sn.sid = m.sid and m.Request! = 0) - Request, is blocked or (sn.sid = m.sid - does not exist locked request, but the locked object is locked by other session requests And m.Request = 0 and LMODE! = 4 and (id1, id2) in (SELECT S.ID1, S.ID2 from V $ LOCK S Where Request! = 0 and s.id1 = m.id1 and s.id2 = m.id2)) Order by ID1, ID2, m.Request;
Through the above query, I know SID and Serial # can kill Alter System Kill Session 'SID, Serial #';
Ayuan
9CBS certified blog expert
Blog expert
Huawei old employees
Big Data
