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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI 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
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');