Oracle – Find Session with Specific Command

Looks like we found the culprit. Following the recent app server upgrade, we inadvertently included both ojdbc14 and ojdbc6 jars into our deployment, and evidently, the jvm picked up ojdbc14 for its Oracle DB driver. Since we removed ojdbc14 manually, this problem hasn't come up again in the past 24 hours. I assume ojdbc14 is no longer officially supported, so it could cause all sorts of funny problems.

Our next step is to find out how ojdbc14 sneaked into the deployment in the first place.

Like ik_zelf said, try to be more explicit.

If you need the session info, for the script that runs from crontab, then the query below will give you some details:

 SELECT 
    a.SID,
    a.USERNAME,
    a.STATUS,
    a.SERVER,
    a.SCHEMANAME,
    a.OSUSER,
    a.MACHINE,
    a.TERMINAL,
    a.PROGRAM,
    a.SQL_EXEC_START
FROM
    v$SESSION a
WHERE
    a.PROGRAM like 'sqlplus%'    
ORDER BY
    a.PROGRAM      

I imagine that the the shell script is using sqlplus.

The output should look like:

1219    SYS ACTIVE  DEDICATED   SYS oracle11    instance_name       sqlplus@instance_name (TNS V1-V3)   8/8/2012 2:41:20 PM
774 SYS INACTIVE    DEDICATED   SYS oracle11    instance_name   pts/1   sqlplus@instance_name (TNS V1-V3)