How to Check Long Running Job in the Oracle Database

Sometimes in the database, few queries are taking a long time to complete the query, to validate which query taking a long time.

Below is the query to check the long-running jobs/queries in the database where:

SOFAR:- Total block Processed.

TOTAL WORK:- Total work Block Processed.
START_TIME:- When job/query started.

Click here to see the sample output of long running jobs.

SET LINE 999 PAGES 999
COL USERNAME FOR A11
COL MESSAGE FOR A45
COL COMPLETED FOr A11
SELECT SOFAR BLOCKS_PROCESSED,TOTALWORK TOTAL_WORK,
TOTALWORK-SOFAR TOTAL_WORK_LEFT,START_TIME START_TIME,ROUND((ELAPSED_SECONDS/60),0) ELAPSED_MINUTES,
SUBSTR(MESSAGE,1,33) MESSAGE,USERNAME, ROUND(SOFAR/TOTALWORK*100,2) || '%' "COMPLETED"
FROM V$SESSION_LONGOPS WHERE TOTALWORK-SOFAR > 0 ORDER BY START_TIME DESC;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

We always encourage the technical person to visit section SCRIPTS to get more daily usage SQL commands.

Hope so you like this Script!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

DATABASE, ORACLE, SCRIPTS, TUNING