شناسایی و 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>

Comment (1)

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد.