Find and Kill Scraping sessions

Firstly find out the offending schemas:

select username, count(*) from gv$session
group by username 
order by 2 desc

Then, if applicable, find the offending machine\terminal

select username, machine, terminal, count(*) from gv$session
where username = 'SCHEMA' 
group by username, machine, terminal
order by 4 desc;

If the sessions are not dying off buy stopping the application\process, generate Kill statements for each session:

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT --s.inst_id,
       --s.sid,
       --s.serial#,
       --p.spid,
       --s.username,
       --s.program,
    'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;'
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' and s.username='SCHEMA';

Full details on how to Kill oracle sessions here

Advertisements

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

%d bloggers like this: