<- Scripts
-- sql.LongRunning.sql
-- Purpose: Show SQL running longer than 1 minute within the last day.
-- -------------------------------------------------------------------
set linesize 240
set pagesize 9999
set verify off
COL elapse FORMAT 999999
COL username FORMAT a15
COL module FORMAT a20
COL sid FORMAT 999999
COL SESSION_SERIAL# FORMAT 999999
COL sql_plan_hash_value FORMAT 9999999999 HEAD 'PHV'
COL action FORMAT a20
-- Process
SELECT to_char(sql_exec_start, 'YYYY-MM-DD HH24:MI') as "STARTED",
round(elapsed_time/60000000,2) as "ELAPSE",
round(cpu_time/1000000/60, 2) as "CPU",
sid,
session_serial# as "SERIAL#",
sql_id,
sql_plan_hash_value,
username,
substr(module,1,15) as "MODULE",
'...' || substr(program,-20) as "PROGRAM",
substr(status,1,15) as "STATUS",
substr(action,1,15) as "ACTION"
FROM v$sql_monitor
WHERE elapsed_time > 60000000
AND username NOT IN('SYS','SYSTEM','COREUSER','OSIATM')
AND sql_exec_start >= sysdate - 1
ORDER BY sql_exec_start DESC;
-- End
prompt
prompt "*** Process Ended ***"
prompt
