شناسایی و KILL کردن SESSION های LOCK کننده در دیتابیس اوراکل

گاهی اوقات برخی OBJECTها یا رکوردهای جداول، به منظور عملیات DML در اختیار یک SESSION خاص قرار گرفته و LOCK شده اند بنابراین کاربران دیگر نمی توانند بر روی آنها از دستورات DML استفاده کنند. در این مواقع می بایست این دسته از SESSIONها را شناسایی کرد و عمل KILL کردن آنها را انجام داد تا پس از ROLLBACK اتوماتیک عملیات، OBJECTها یا رکوردهای مورد نیاز آزاد گردند.

در ادامه دو روش مختلف برای انجام عملیات شناسایی و KILL کردن SESSIONهای LOCK کننده معرفی می کنیم.

روش 1:

برای آنکه بتوان مشخصات SESSIONهای LOCK کننده وOBJECTهای LOCK شده توسط آنها را مشاهده نمود می توان از QUERY زیر استفاده کرد:

select

b.USERNAME as “OWNER OF SESSION”,

c.owner “OWNER OF OBJECT”,

c.object_name ,

c.object_type,

b.sid,

b.serial#,

b.status,

b.osuser,

b.machine

from

v$locked_object a ,

v$session b,

dba_objects c

where

b.sid = a.session_id

and

a.object_id = c.object_id;

با جایگزینی SID و #SERIAL مربوط به SESSION مورد نظر در دستور زیر، می توان عمل KILL را انجام داد:

alter system kill session ‘SID,SERIAL#‘ immediate;

برای اجرا شدن سناریوی بالا، کاربری که عمل شناسایی و KILL کردن SESSION را انجام می دهد باید دارای مجوز DBA باشد. البته می توان بجای مجوز DBA فقط مجوزهای ALTER SYSTEM و SELECT ANY DICTIONARY را GRANT کرد ولی به هر حال این روش از لحاظ امنیتی دارای برخی مشکلات می باشد. بنابراین بهتر است از روش دوم استفاده نمود.

روش 2:

در ابتدا با کاربر sys که تمام مجوزهای دیتابیس را دارد متصل می شویم.

مرحله 1. یک VIEW براساس QUERY بالا ایجاد می کنیم:

SQL> show user;

User is “SYS”

SQL>

SQL> create or replace view locking_sessions as select

b.USERNAME as “OWNER OF SESSION”,

c.owner “OWNER OF OBJECT”,

c.object_name ,

c.object_type,

b.sid,

b.serial#,

b.status,

b.osuser,

b.machine

from

v$locked_object a ,

v$session b,

dba_objects c

where

b.sid = a.session_id

and

a.object_id = c.object_id;

View created

SQL>

مرحله 2. یک STORED PROCEDURE که دستور ALTER SYSTEM را با استفاده از DYNAMIC SQL اجرا می کند می سازیم:

SQL> show user;

User is “SYS”

SQL>

SQL> create or replace procedure kill_session

(pn_sid number

,pn_serial number)

as

lv_user varchar2(30);

begin

select username into lv_user from v$session where sid = pn_sid and serial# = pn_serial;

if lv_user is not null and lv_user not in (‘SYS’,’SYSTEM’) then

execute immediate ‘alter system kill session ”’||pn_sid||’,’||pn_serial||””;

else

raise_application_error(-20000,’Attempt to kill protected system session has been blocked.’);

end if;

end;

/

Procedure created

SQL>

مرحله 3. مجوز SELECT برای VIEW و EXECUTE برای پروسیجر مرحله 1 و 2 را به کاربرانی که می خواهند عملیات شناسایی و KILL کردن را انجام دهند GRANT می کنیم:

SQL> show user;

User is “SYS”

SQL> create public synonym locking_sessions for sys.locking_sessions;

Synonym created

SQL> create public synonym kill_session for sys.kill_session;

Synonym created

SQL> grant select on locking_sessions to milad;

Grant succeeded

SQL> grant execute on kill_session to milad;

Grant succeeded

تست روش دوم:

کاربر ahmad یکی از جداول دیتابیس (milad.test) را lock می کند:

SQL> show user;

User is “ahmad”

SQL> select * from milad.test for update;

PERSONEL NAME

———- —————————————-

SQL>

کاربر milad می تواند عملیات شناسایی و KILL کردن را انجام دهد:

SQL> show user;

User is “milad”

SQL> select object_name,SID, SERIAL# from locking_sessions;

object_name SID SERIAL#

——- ——- —–

TEST 26 7

SQL> exec kill_session(26,7);

PL/SQ procedure successfully completed

SQL> select * from locking_sessions;

No rows selected

SQL>