Search oracle 'ghost' or hang connection with sql.

Here's a quick sql on how to search for session details including their running sql. The sql statement is as below :

select a.terminal, a.program, a.username, to_char((a.last_call_et/3660),990.99) LastCall, a.logon_time, a.sql_hash_value,

decode(a.sql_hash_value,'0',null,(select b.sql_text from v$sql b where b.hash_value = a.sql_hash_value and rownum = 1)) SQL_statement

from v$session a

where a.terminal like '%'

order by 4 desc;

a.terminal - machine name

a.program - what program taht make connection to our database.

LastCall - last time this connection make an sql call. The column is in seconds so we devide with 3660 to convert it to hours.

a.logon_time - logon time for this particular connection.

a.sql_hash_value - the key to link between v$sql & v$session. Just for checking purpose.

SQL_statement - sql statement called by this connection.

So, basically this sql will retrieve all connections sorted by 'LastCall' since i want to get the oldest connection. If you want to drill down to specific machine just replace '%' with a machine name from column 'a.terminal'. Easy huh! I suggest you use TOAD or SQL Developer to enhance your plsql experience.