Oracle - How can I find the session id of a shell script (Linux)?

Question

I have a Linux script run periodically from crontab which executes some sql scripts. How can I get/identify the session of that running job?

thanks

Answer 1

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)   

Answer 2

In order to find a specific session running a certain script, it is easiest if the scripts makes itself identifiable by using dbms_application_info and dbms_session. See Morgans Library for details. Also see track the parts of my application that are in use for sample code.

Doing so enables you to select on v$session and filter not only on username and machine but also on module, action, client_info and client_id. Username and machine are not mutable but the other columns are controlled by dbms_application_info and dbms_session. Using those makes your script recognizable. This of course, only works when the other session has select privileges on v_$session.

dbms_application_info is also great for finding where your code is running; a way to instrumentate your code to make performance analysis easier.

your code in SQLdeveloper would be as simple as

select sid 
from gv$session
where module = 'what you specified'
order by sid
/

The code added in the script to make this work would be

exec dbms_application_info.set_client_info('some detail'); 
exec dbms_application_info.set_module('what you specified','action'); 
exec dbms_session.set_identifier('DDD');