Continuing the ORACLE DATABASE session I will post another quick but functional balcony, which is a query
which shows you which sessions are active in Oracle and how to delete these sessions.
Usually this need arises when:
- Your development team is using a base (either Production / Homologation / Development). This can happen, for example, when some instruction that accesses the bank does not close the connection after obtaining the desired information. So with the open connection you can generate an unnecessary number of active sessions.
- Another need is when you need to stop processes / jobs that are running in the database and that, for some reason, need to be stopped abruptly.
So every time you want to identify the users (sessions) that are active (connected in your bank) you can use the query below :
SELECT s.username,
s.osuser,
s.sid,
s.serial #,
p.spid,
s.status,
s.machine,
s.program,
TO_CHAR (s.logon_Time, ‘DD-MON-YYYY HH24: MI: SS’) AS logon_time
FROM v $ sessions
inner join v $ process p
on s.paddr = p.addr
WHERE s.status = ‘ACTIVE’
and s.type <> ‘BACKGROUND’
Realizing that the number of connected users is high and after analyzing the accesses realize who is or is not using the session, you can “tear them down” by running the script below:
ALTER SYSTEM KILL SESSION ‘sid, serial #’ IMMEDIATE;
That’s it, I hope it helped.
Inquiries, please contact us.
Strong hug.