Helps you free yourself from troubles: Discover and lift the deadlock in Oracle databases

Although Oracle's locking mechanism is well-developed, many people are stuck in database programming. Don't blame others. Let's take a closer look at your design model and operational logic. The problem lies in yourself.

The following program can help you remove deadlocks to solve your urgent needs. The cause of the deadlock will be discussed in detail in subsequent articles.

 

@ Echo off
Echo Author: gmtsao Date: 2004-10-19 17:08
Echo *************************************** *******************
Echo ******************************
Echo **********************************
Echo ********************************
Echo **********************
Echo ********************************
Echo **********************************
Echo *******************************
Echo *************************************** *******************
Echo usage: Save all text as the CCs. BAT file and execute ccs. Bat UID/pwd @ servname.
Echo Oracle Database unlock...

Set ARGs = ""

If "% 1" = "" Goto end
Set ARGs = % 1

: Loop
Shift
If "% 1" = "" Goto end
Set ARGs = % ARGs % 1
Goto Loop

: End

If % ARGs % = "" Goto cancel

Del/q c:/ckutl. CC

Echo create or replace procedure sys. prc_kill_locked (P1 number default 0, P2 number default 0) as> C:/ckutl. CC
Echo v_sid number;> C:/ckutl. CC
Echo v_serial number;> C:/ckutl. CC
Echo v_ SQL varchar2 (4000);> C:/ckutl. CC
Echo type curobj is ref cursor;> C:/ckutl. CC
Echo ccobj curobj;> C:/ckutl. CC
Echo --> C:/ckutl. CC
Echo begin> C:/ckutl. CC
Echo if P1 is not null and P2 is not null and P1! = 0 and P2! = 0 then> C:/ckutl. CC
Echo v_ SQL: = Concat ('alter system kill session ''', P1);> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, ',');> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, P2);> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, ''');> C:/ckutl. CC
Echo execute immediate v_ SQL;> C:/ckutl. CC
Echo return;> C:/ckutl. CC
Echo end if;> C:/ckutl. CC
Echo --> C:/ckutl. CC
Echo v_ SQL: = 'select Sid, serial # from V $ session where Sid in (select Sid from V $ lock) ';> C:/ckutl. CC
Echo/*> C:/ckutl. CC
Echo v_ SQL: = 'select/* + no_merge (a) no_merge (B) no_merge (c) */. sid,. serial # ';> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, 'From v $ session A, V $ lock B, V $ sqltext C');> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, 'where. username is not null and. lockwait = B. kaddr and C. hash_value =. SQL _hash_value ');> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, 'join');> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, 'select/* + no_merge (a) no_merge (B) no_merge (c) */. sid,. serial # ');> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, 'From v $ session A, V $ lock B, V $ sqltext C');> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, 'where B. id1 in (');> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, 'select/* + no_merge (d) no_merge (e) */distinct E. id1 ');> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, 'From v $ Session D, V $ lock e');> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, 'where D. lockwait = E. kaddr ');> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, ') and. username is not null and. SID = B. sid and B. request = 0 and C. hash_value =. SQL _hash_value ');> C:/ckutl. CC
Echo */> C:/ckutl. CC
Echo --> C:/ckutl. CC
Echo open ccobj for v_ SQL;> C:/ckutl. CC
Echo --> C:/ckutl. CC
Echo loop> C:/ckutl. CC
Echo fetch ccobj into v_sid, v_serial;> C:/ckutl. CC
Echo exit when ccobj % notfound;> C:/ckutl. CC
Echo v_ SQL: = Concat ('alter system kill session ''', v_sid);> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, ',');> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, v_serial);> C:/ckutl. CC
Echo v_ SQL: = Concat (v_ SQL, ''');> C:/ckutl. CC
Echo execute immediate v_ SQL;> C:/ckutl. CC
Echo end loop;> C:/ckutl. CC
Echo close ccobj;> C:/ckutl. CC
Echo exception> C:/ckutl. CC
Echo when others then> C:/ckutl. CC
Echo NULL;> C:/ckutl. CC
Echo end;> C:/ckutl. CC
Echo/> C:/ckutl. CC
Echo exec SYS. prc_kill_locked> C:/ckutl. CC
Echo/> C:/ckutl. CC
Echo drop procedure sys. prc_kill_locked> C:/ckutl. CC
Echo/> C:/ckutl. CC
Echo quit> C:/ckutl. CC
Echo/> C:/ckutl. CC

Sqlplus % ARGs % @ C:/ckutl. CC

Del/q c:/ckutl. CC

@ Echo on
Exit

: Cancel