گاهی اوقات برخی 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>
خیلی خیلی کاربردی، ممنون