Query to find running procedure name with duration

 

       


SELECT vs.username,d_o.object_name ,vs.sid,vs.LOGON_TIME ,
  ROUND((SYSDATE    -LOGON_TIME)*(24*60),2) AS MINUTES_LOGGED_ON,
  ROUND(LAST_CALL_ET/60,2)                  AS Minutes_FOR_CURRENT_SQL,
  vs.BLOCKING_SESSION,vs.EVENT,vs.STATUS,vs.SQL_ID,'CALLED PLSQL'
FROM dba_objects d_o
INNER JOIN v$session vs
ON d_o.object_id = vs.plsql_entry_object_id

UNION ALL

SELECT vs.username,d_o.object_name ,vs.sid,vs.LOGON_TIME ,
  ROUND((SYSDATE    -LOGON_TIME)*(24*60),2) AS MINUTES_LOGGED_ON,
  ROUND(LAST_CALL_ET/60,2)                  AS Minutes_FOR_CURRENT_SQL,
  vs.BLOCKING_SESSION,vs.EVENT,vs.STATUS,vs.SQL_ID,'CURRENT PLSQL'
FROM dba_objects d_o
INNER JOIN v$session vs
ON d_o.object_id = vs.plsql_object_id
ORDER BY MINUTES_LOGGED_ON DESC,OBJECT_NAME



-----------------in HOUR

SELECT vs.username,d_o.object_name ,vs.sid,vs.LOGON_TIME ,
  ROUND((SYSDATE    -LOGON_TIME)*(24*60)/60,2) AS HOURS_LOGGED_ON,
  ROUND(LAST_CALL_ET/60,2)                  AS Minutes_FOR_CURRENT_SQL,
  vs.BLOCKING_SESSION,vs.EVENT,vs.STATUS,vs.SQL_ID,'CALLED PLSQL'
FROM dba_objects d_o
INNER JOIN v$session vs
ON d_o.object_id = vs.plsql_entry_object_id

UNION ALL

SELECT vs.username,d_o.object_name ,vs.sid,vs.LOGON_TIME ,
  ROUND((SYSDATE    -LOGON_TIME)*(24*60)/60,2) AS HOURS_LOGGED_ON,
  ROUND(LAST_CALL_ET/60,2)                  AS Minutes_FOR_CURRENT_SQL,
  vs.BLOCKING_SESSION,vs.EVENT,vs.STATUS,vs.SQL_ID,'CURRENT PLSQL'
FROM dba_objects d_o
INNER JOIN v$session vs
ON d_o.object_id = vs.plsql_object_id
ORDER BY HOURS_LOGGED_ON DESC,OBJECT_NAME
;


---------------WITH SQL TEXT

SELECT vs.username,d_o.object_name ,vs.sid,vs.LOGON_TIME ,
  ROUND((SYSDATE    -LOGON_TIME)*(24*60)/60,2) AS HOURS_LOGGED_ON,
  ROUND(LAST_CALL_ET/60,2)                  AS Minutes_FOR_CURRENT_SQL,
  vs.STATUS,SS.SQL_TEXT,vs.SQL_ID,vs.BLOCKING_SESSION,vs.EVENT,'CALLED PLSQL'
FROM dba_objects d_o
INNER JOIN v$session vs
ON d_o.object_id = vs.plsql_entry_object_id
LEFT JOIN V$SQL SS
ON SS.SQL_ID=VS.SQL_ID
;