Kill sessions

Vcloud kill idle sessions

Sometimes…….

VMware products don’t get close sessions in the database once they’re finished. We won’t point fingers but if you need to get rid of these sessions. Here’s an idea of how!

Oracle….

This step creates the procedure

As sys or system create the following procedure

create or replace procedure kill_sessions  (p_username varchar2, p_older_than number)

AS

—————————————————————————–

— Procedure       : kill_sessions older than passed in numbe of days

— Description     : This procedure should only be executed until the change can be made on the cells

—————————————————————————–

 

v_sid                   integer;

cursor get_sessions is

select s.sid sid, s.serial# ser

from v$session s

where username = p_username

and status=’INACTIVE’;

–and floor(last_call_et / 60 /60 /24) > p_older_than;

 

session_rec get_sessions%ROWTYPE;

 

lv_stmt varchar(5000);

 

BEGIN

 

FOR session_rec in get_sessions LOOP

BEGIN

lv_stmt := ‘ALTER SYSTEM KILL SESSION ”’|| session_rec.sid || ‘,’ ||session_rec.ser ||””;

dbms_output.put_line(lv_stmt);

EXECUTE IMMEDIATE lv_stmt;

end;

end loop;

END kill_sessions;

/

 

show errors;

/

 

 

 

set serveroutput on;

 

 

This step executes the procedure

 

execute kill_sessions(‘CLOUDBOSS’,3);

 

  • This will kill sessions older than 3 days old.

 

 

Here’s the test

 

  • I tested this with a vc see below

 

SQL> select username,floor(last_call_et / 60) from v$session  where  status=’INACTIVE’;

 

USERNAME                       FLOOR(LAST_CALL_ET/60)

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

SYSTEM                                          24270

SYS                                                 2

SYS                                                 2

SYS                                                 3

VPXADMIN                                        21712

SYS                                                 2

SYS                                                 2

SYS                                                 2

SYSTEM                                          21612

VPXADMIN                                        18828

SYS                                                 2

 

SQL> set serveroutput on;

SQL> execute kill_sessions(‘VPXADMIN’, 3);

ALTER SYSTEM KILL SESSION ‘136,11478’

ALTER SYSTEM KILL SESSION ‘264,22929’

 

PL/SQL procedure successfully completed.

 

SQL>  select username,floor(last_call_et / 60) from v$session  where  status=’INACTIVE’;

 

USERNAME                       FLOOR(LAST_CALL_ET/60)

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

SYSTEM                                          24272

SYS                                                 4

SYS                                                 4

SYS                                                 5

SYS                                                 4

SYS                                                 4

SYS                                                 4

SYSTEM                                          21614

SYS                                                 0

SYS                                                 4

SYS                                                 5

 

11 rows selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s