This script helps in identifying the session which is running on Oracle database from long time. In various performance issue scenario, DBA and application developer is keen to know about the session which is taking long time.
set pages 10000
set lines 180
col USERNAME for a25
col SESSION_RUNNING_FROM for a50
SELECT s.SID,s.SERIAL#,s.USERNAME,s.STATUS,s.SQL_ID,floor((sysdate-s.logon_time)*24) || ' HOURS ' || mod(floor((sysdate-s.logon_time)*24*60),60) || ' MINUTES ' || mod(floor((sysdate-s.logon_time)*24*60*60),60)|| ' SECS ' Session_running_since FROM v$session s where s.username<>'SYS' order by SESSION_RUNNING_SINCE desc;
With the above output, it becomes easier to identify the session which is running from long time. Session_running_since column will show the session running in Hours:Minutes:Seconds(HH:MM:SS) format. Also sid and sql_id is available to investigate further.
set pages 10000
set lines 180
col USERNAME for a25
col SESSION_RUNNING_FROM for a50
SELECT s.SID,s.SERIAL#,s.USERNAME,s.STATUS,s.SQL_ID,floor((sysdate-s.logon_time)*24) || ' HOURS ' || mod(floor((sysdate-s.logon_time)*24*60),60) || ' MINUTES ' || mod(floor((sysdate-s.logon_time)*24*60*60),60)|| ' SECS ' Session_running_since FROM v$session s where s.username<>'SYS' order by SESSION_RUNNING_SINCE desc;
| SID | SERIAL# | USERNAME | STATUS | SQL_ID | SESSION_RUNNING_SINCE |
|---|---|---|---|---|---|
| 158 | 30 | ASHISH | ACTIVE | 90ya7z9gh6cx1 | 0 HOURS 48 MINUTES 44 SECS |
| 152 | 34 | ASHISH | INACTIVE | 0 HOURS 40 MINUTES 20 SECS |
With the above output, it becomes easier to identify the session which is running from long time. Session_running_since column will show the session running in Hours:Minutes:Seconds(HH:MM:SS) format. Also sid and sql_id is available to investigate further.
No comments:
Post a Comment