Oracle uses the stored procedure to kill the session to solve the table lock problem.

I believe everyone has met the lock table. I think everyone knows how to unlock the table. However, there are some low-level kill processes. We use stored procedures to implement this function.

I believe everyone has met the lock table. I think everyone knows how to unlock the table. However, there are some low-level kill processes. We use stored procedures to implement this function.

I believe everyone has encountered the lock table. I think everyone knows the unlock method, but the killing processes are somewhat low-level. We use stored procedures to implement this function, it becomes very simple.

1. Create a view to query process information. Be sure to grant the current user the system permission to select any dictionary.

Create or replace view mysession
Select B. sid, B. serial # from v $ lock a, v $ session B where a. SID = B. SID and status = 'inactive'
-- And program <> 'plsqldev.exe'
Order by machine;

2. Create a stored procedure and cycle the data in the attempt above to kill them one by one. Note that the current user must grant the system permission to alter system

Create or replace procedure kill_session is
V_sid varchar2 (30 );
V_serial varchar2 (30 );
V_ SQL varchar2 (1000 );
TYPE DyData is ref cursor;
Rows DyData;
Begin

V_ SQL: = 'select * from mysession ';
OPEN rows FOR v_ SQL;
LOOP
FETCH rows
Into v_sid, v_serial;

V_ SQL: = 'alter system kill session ''' | v_sid | ',' | v_serial | '''';
Execute immediate v_ SQL;
-- Dbms_output.put_line (v_userid | '_' | v_user | '_' | v_pwd );

Exit when rows % NOTFOUND;
End loop;

End kill_session;

3. Execute the Stored Procedure

Begin
-- Call the procedure
Kill_session;
End;

Note that sometimes the process may be killed. You only need to modify the view in the first step and exclude yourself.

Related reading:

Oracle 10 DBMS_MONITOR tracks and analyzes other session SQL statements

Quickly locate trc files generated by other sessions

Oracle kill session

MySQL Master/Slave troubleshooting-session-level parameter replication error

How to view session blocking of Oracle Database