Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.