Oracle Script For Getting Long Active Sessions

Question

Is there any script that you could provide for v$session? I seem to be encountering an issue in which my oracle active sessions are filled up. This is when I do

SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE';

Here is my problem : I need a script which could determine how long the connection was held and who was holding it.

I wonder where I'm getting it. If possible, it would be really nice if I could autodial these sessions. I'd like to know how long they were held since the last command was done. Another thing to note: There is no current statement associated with the SQL sessions I'm seeing.

Thanks

Answer 1

Presumably they are active and doing things so looks like you might need to get more info about your sessions before killing them off. I usually use a query like this to get important info about active sessions including how long they have been logged on (oldest at top), and also how long they have been running the current SQL.

SELECT USERNAME, 
       TERMINAL, 
       PROGRAM, 
       SQL_ID, 
       LOGON_TIME, 
       ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON, 
       ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL  
  From v$session 
 WHERE STATUS='ACTIVE' 
   AND USERNAME IS NOT NULL
ORDER BY MINUTES_LOGGED_ON DESC;