Find unclosed Oracle SQL connections

find-unclosed-queries-osql.sql

-- A way to find un-closed Oracle SQL queries
-- Taken from https://srivenukadiyala.wordpress.com/2011/02/15/one-way-to-detect-jdbc-connection-leaks/

-- Get a count of sessions per machine:
select 	machine, count(*)
from 	v$session
where 	username = 'user name'
and 	machine like 'machine name'
and 	program = 'JDBC Thin Client'
group by machine
;

-- Get hash values and runtime details about queries counted:
col sid head "Sid" form 9999 trunc
col serial# form 99999 trunc head "Ser#"
col username form a15 trunc
col machine head "Client|Machine" form a20 trunc
col login head "Login" form a11
col sql_hash_value head "Curr|Hash|Value" form 9999999999
col prev_hash_value head "Prev|Hash|Value" form 9999999999
col "last call"  form 9999999 trunc head "Last Call|In Secs"
col status form a6 trunc

select	sid, serial#, username, machine,
        to_char(logon_time,'ddMon hh24:mi') login,
        SQL_HASH_VALUE, PREV_HASH_VALUE,
        last_call_et "last call", status
from 	v$session
where 	username = 'user name'
and 	machine = 'machine name'
and 	program = 'JDBC Thin Client'
order 	by logon_time
;

-- Copy hash here to determine the SQL causing the issue:
select * from v$sql where hash_value = '<the hash here>';

-- To check the max number of sessions allowed for the database:
SELECT name, value 
FROM v$parameter
WHERE name = 'sessions';