ORA-01031 insufficient privileges kill database session using procedure


Oracle database users remains connected for longer hours without performing any activity which consumes User session/PGA memory.

As a best practice such hovering session should be terminated immediately to reduce burden on session/process resource limit.


Either of the two procedures can be used to kill inactive sessions on database & same can be scheduled using dbms_scheduler. 


Procedure 1:

create or replace procedure session_kill_proc
as
c1_sid number;
c1_serial number;
cursor c1 is select vs.sid,vs.serial#
from v$session vs
where vs.status in ( 'INACTIVE')
and vs.last_call_et/60 > 2
and upper(vs.service_name) not like '%BACKGROUND%'
and upper(vs.username) like 'DB_READ%' ;
begin
open c1;
loop
fetch c1 into c1_sid , c1_serial;
exit when c1%notfound;
dbms_output.put_line( 'Session about to kill :' || c1_sid ||' , '|| c1_serial);
execute immediate 'alter system kill session ''' || c1_sid || ',' || c1_serial || '''';
end loop;
close c1;
end;
/


Procedure 2:

create or replace procedure 
session_kill_proc
as
begin
for r in (select vs.sid,vs.serial#
from v$session vs
where vs.status in ( 'INACTIVE')
and vs.last_call_et/60 > 2
and upper(vs.service_name) not like '%BACKGROUND%'
and upper(vs.username) like 'DB_READ%')
loop
dbms_output.put_line( 'Session about to kill :' || r.sid ||' , '|| r.serial# );
execute immediate 'alter system kill session ''' || r.sid || ',' || r.serial# || '''';
end loop;
end;
/

Schedule a Job: To run every midnight

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'kill_db_reader_session',
job_type => 'STORED_PROCEDURE',
job_action => '
session_kill_proc',
start_date => '16-OCT-20 12.30.00AM',
repeat_interval => 'FREQ=DAILY;INTERVAL=1',
enabled => TRUE,
comments => 'Job to kill DB inactive sessions');
END;
/


Test Manually:


BEGIN
session_kill_proc;
END;
/

Or

BEGIN 
DBMS_SCHEDULER.RUN_JOB( JOB_NAME => 'kill_db_reader_session', 
USE_CURRENT_SESSION => FALSE); 
END; 
/



Verify:

select vs.sid,vs.serial#
from v$session vs
where vs.status in ( 'INACTIVE')
and vs.last_call_et/60 > 2
and upper(vs.service_name) not like '%BACKGROUND%'
and upper(vs.username) like 'DB_READ%';


ORA-01031 insufficient privileges error while running job or testing manually.. While executing alter system kill session '<sid>,<serial#>'; it works fine with no error but in procedure call or Job run it gives error.





Solution for the same to grant alter system privilege to user executing job or under which procedure is executed.


grant alter system to db_admin;


Note: Comment out dbms_output from procedure once testing is complete to avoid buffer overflow




No comments:

Post a Comment