CODEWITHSUNDEEP
oracle-databaseoracle11g
user3496230
user3496230

Reputation:

How to see and kill a list of sessions being made to an ORACLE schema?

I am attempting to diagnose an issue and for that need to see the list of connections being made to a specific Oracle Schema.

Assuming that I have DBA privileges, what are the queries that I should run to :

  1. List the sessions (active and otherwise) to an Oracle schema and,
  2. Kill these sessions

Thank you!

Upvotes: 2

Views: 1506

Answers (1)

user3501720
user3501720

Reputation:

Here are the queries you will need to execute:

-- 1. Check connected sessions
select     sid, serial#, username, machine,
    to_char(logon_time+5/24,'ddMon hh24:mi') login,
    SQL_HASH_VALUE, PREV_HASH_VALUE,
    status
from  v$session
where
lower(username) like  '%SCHEMA_NAME%'
--and lower(status) not like '%killed%'
--and machine like '%SOURCE_MACHINE_NAME%'
order      by logon_time;

-- 2. Same as above, but just show the count of sessions  
select    count(1)
from      v$session
where      lower(username) like  lower('%SCHEMA_NAME%')
--and lower(status) not like '%inactive%'
order      by logon_time;


-- 3. Kill connected sessions
ALTER SYSTEM ENABLE RESTRICTED SESSION;
begin
    for x in (
            select Sid, Serial#, machine, program
            from v$session
            where      lower(username) like  '%SCHEMA_NAME%'
        ) loop
        execute immediate 'Alter System Kill Session '''|| x.Sid
                     || ',' || x.Serial# || ''' IMMEDIATE';
    end loop;
end;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
-- May have to wait for a bit for the killed sessions to be cleaned up

I have been using the above on an Oracle 11g database so would expect them to work for you too.

Note that I have included some commented out where clauses in the first two queries which would allow you to refine the search criteria.

Hope this is what you were looking for.

Upvotes: 2

Related Questions