What are my batch UBEs doing in the Oracle database?

The Oracle DBA is often asked to find out why a UBE is running so long, or if it's running at all inside the Oracle database.  A good place to start is to locate all UBEs that have database connections, and check what they are doing.  The following simple query can be used to locate the database connections:

column process  heading "Process|Id"            format a10
column sid      heading "Oracle|SID"            format 999999
column event    heading "Wait Event"            format a10 wrap
column machine  heading "Client|Machine"        format a10 wrap
column program  heading "Program"               format a10 wrap
column time     heading "Logon|Time"            format a20

set pagesize 9999

select  process,
        sid, 
        substr(event,1,80) event,
        substr(machine,1,20) machine,
        substr(program,1,30) program,
        to_char(logon_time,'mm/dd/yyyy hh24:mi:ss') time
from    gv$session
where   program like 'runbatch%'
order   by 1,logon_time

Of particular importance is the Process Id, which indicates the operating system process number of the UBE application, and the Wait Event, which indicates what the database session is doing.  These bits of information will help the DBA determine if the UBE is active, and what it might be waiting on inside the database.

The above information can also be connected to the JDE job table to show the name of the UBE that is running, and to reveal exactly when the job was submitted.