This content has been marked as final. Show 8 replies
Thanks, I see that V$session has what I need.
In the following example:
cursor A is
where blocking_session is not null
group by blocking_session;
for a1 in a loop
What is the command and syntax I would use for xxxxxxxxxxxxxxxxxxxxxxx?
Would I need a "WHERE sid = a.blocking_session" statement
or would I be able to embed a.blocking_session into the command line?
I currently use TOAD for Oracle to kill sessions and I allways have the "Immediate"
checkbox checked, so the command in the script I'd like to have immediate also.
You can use something like this
Do not forget to use your own SQL statement in the CURSOR!.
SQL> DECLARE 2 CURSOR c IS 3 SELECT c.owner, 4 c.object_name, 5 c.object_type, 6 b.SID, 7 b.serial#, 8 b.status, 9 b.osuser, 10 b.machine 11 FROM v$locked_object a, v$session b, dba_objects c 12 WHERE b.SID = a.session_id AND a.object_id = c.object_id 13 and c.object_name in ('JSW_CRM_C_HR_COIL_INFO','T_SPCL_BARCODE_CRS2'); 14 c_row c%ROWTYPE; 15 l_sql VARCHAR2(100); 16 BEGIN 17 OPEN c; 18 LOOP 19 FETCH c INTO c_row; 20 EXIT WHEN c%NOTFOUND; 21 l_sql := 'alter system kill session '''||c_row.SID||','||c_row.serial#||''''; 22 EXECUTE IMMEDIATE l_sql; 23 END LOOP; 24 CLOSE c; 25 END; 26 /
This is easier said than done. Numerous reasons. Here's a couple:
You cannot rely on using v$session to identifying blocked sessions.
Some user sessions may be blocked by a system process (e.g. log writer), which means you cannot unblock the user sessions by killing the blocking session. (especially when running into an Oracle bug where a user session will spin on "+enq: RO - fast object reuse+" when dropping an object and then being shown as blocked by a system session/process).
Killing a session in Oracle is a misnomer as the "+alter system kill session+" is a request and not a command. The relevant user session needs to terminate itself (kind of like assisted suicide). No other process actually kills the physical process (or thread) executing that user session. Thus if that user session is waiting on some external response or event, it will be unable (now and forever) to respond to that kill request. In such a case, the only alternative is killing the physical process instead. (or shutting down the database)
A blocking session could be a job process that contains problematic code that leads to the blocking/serialisation. Killing that session will simply fail the job after which the job queue manager will restart the session again.. 16 times in succession. Only then will it mark that job entry as broken and cease trying to restart it.
So killing blocking sessions? It is not easy to identify blocking processes, they cannot always be killed, and even when you do kill them, they may simply reincarnate as a new session.
So what is the solution then?
Blocking sessions are not "+A Bad Thing+". There are very good and technically sound reasons for serialisation. When it does become a problem, there's something more to it. And blindly killing blocking sessions in such a case is treating the symptom of a problem and ignoring the problem all together. Find the reason for this serialisation and address that.
True enough, however given the requirement to <i>"give the trainer the ability to kill all the blocking sessions when a block(s) is holding up the entire class"</i>, in a training environment that could presumably be restarted or reinstalled from scratch without causing major problems, I don't think it's that unreasonable to implement a kill-'em-all button. Presumably the query of v$session could be refined to include only foreground sessions logged in as a particular user, e.g. (untested):
BEGIN FOR r IN ( SELECT 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE' AS ddl FROM v$session s AND s.blocking_session IS NOT NULL AND s.username LIKE 'TRAIN%' ) LOOP DBMS_OUTPUT.PUT_LINE(r.ddl); EXECUTE IMMEDIATE r.ddl END LOOP; END;