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?
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)
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');