How to check which scheduler job run longer than 1000 seconds.

Today we found scheduler job running very long time, and I want to create script to check which job has been running more than 1000 seconds.


desc ALL_SCHEDULER_RUNNING_JOBS;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
JOB_NAME NOT NULL VARCHAR2(30)
JOB_SUBNAME VARCHAR2(30)
SESSION_ID NUMBER
SLAVE_PROCESS_ID NUMBER
SLAVE_OS_PROCESS_ID VARCHAR2(12)
RUNNING_INSTANCE NUMBER
RESOURCE_CONSUMER_GROUP VARCHAR2(32)
ELAPSED_TIME INTERVAL DAY(3) TO SECOND(2)
CPU_USED INTERVAL DAY(3) TO SECOND(2)

SQL> select JOB_NAME, ELAPSED_TIME from ALL_SCHEDULER_RUNNING_JOBS where ELAPSED_TIME > INTERVAL '1000' SECOND ;

JOB_NAME ELAPSED_TIME
------------------- ---------------------------------------------------------------------------
JOB_XMLSEND_LDAP +001 13:43:31.84
SENDXML_COLLECTION +004 13:33:31.91
SENDXML_CHARGING +004 21:00:42.84


-- TaTsHuYa --