Oracle Lock solution

You can use spotlight software to monitor the running status of the database.

When a session lock occurs, we need to handle it in time.

1. view which session locks:
SQL statement: Select 'alter system kill session ''' | Sid | ',' | serial # | '''; 'From v $ session where Sid in (select Sid from V $ lock where block = 1 );

SQL> select 'alter system kill session ''' | Sid | ',' | serial # | '''; 'From v $ session where Sid in (select Sid from V $ lock where block = 1 );
'Altersystemkillsession ''' | Sid | ',' | serial # | ''';'
--------------------------------------------------------------------------------
Alter system kill session '123 ';
Alter system kill session '2017 05 ';
Alter system kill session '123 ';
Alter system kill session '2017 0 ';

2. view the session lock.
SQL statement: Select S. Sid, Q. SQL _text from V $ sqltext Q, V $ session s
Where Q. Address = S. SQL _address
And S. Sid = & SID
Order by piece;

SQL> select S. sid, Q. SQL _text from V $ sqltext Q, V $ session s where Q. address = S. SQL _address and S. sid in (select Sid from V $ lock where block = 1) Order by piece;
Sid SQL _text
--------------------------------------------------------------------------
77 update profile_user Set ID = 1, company_id = 2, customer_id = 3, named
77 _ insured_id = 4, login = 5, role_id = 6, password = 7, email = 8, time_zon
77 E = 9 where profile_user.id =: 34
3 rows selected.

3. Kill the lock process.
SQL statement: Alter system kill session '000000 ';

SQL> alter system kill session '000000 ';
System altered.

4. Check who has locked the lock.
Select s1.username | [email = '@'] '@' [/Email] | s1.machine
| '(SID =' | s1.sid | ') is blocking'
| S2.username | [email = '@'] '@' [/Email] | s2.machine | '(SID =' | s2.sid | ')' As blocking_status
From v $ lock L1, V $ session S1, V $ lock L2, V $ session S2
Where s1.sid = l1.sid and s2.sid = l2.sid
And l1.block = 1 and l2.request> 0
And l1.id1 = l2.id1
And l2.id2 = l2.id2;

Note:
>: Redirected output: The standard output of the file is output to the file again, or the data file is used as the standard input content of another program.
|: Unix pipeline: outputs a file as the input of another file.

Try executing the SQL statement: Alter system kill session '000000' (SID: 391,483); Be careful when Sid is lower than 391. This process may correspond to an application, you can kill a transaction.

 

 

------------------------------------------------------ Csdn user's SQL -------------------------------

 

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, 'clusive ',
Lmode, ltrim (to_char (lmode, '123 '))
) Lmode,
Decode (M. Request,
0, 'none ',
1, 'null ',
2, 'row share ',
3, 'row excl .',
4, 'share ',
5,'s/row excl .',
6, 'clusive ',
Request, ltrim (to_char (M. Request, '20140901 '))
) Request,
M. id1, M. Id2
From v $ session Sn, V $ lock m
Where (SN. Sid = M. Sid and M. Request! = 0) -- the lock request is blocked.
Or (SN. Sid = M. Sid -- no lock request exists, 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;

Alter system kill session 'sid, serial #';