Peoplesoft - Process Status
The below few lines will help us know the Process Status of a process that is currently active...
1. Use the below SQL to correctly identify the Oracle session of a currently active process.
SELECT CLIENT_INFO, PROGRAM, SID, SERIAL#, SQL_ADDRESS FROM V$SESSION WHERE CLIENT_INFO LIKE 'SAN%' AND STATUS = 'ACTIVE';
2. We can use the SID from the above SQL in the below SQL statement to get the SQL being executed currently ...
SELECT SQL_TEXT FROM V$SQLTEXT WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID= 98) ORDER BY PIECE;
Also, there are scenarios where the session might not be executing any SQL because it is busy executing others stuff in Code, or else it might be in the Queued or Posting State...