2

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

  • 1
    are you looking for a way to find your current session (from within the script) or find the session of the running script from an other session? – ik_zelf Aug 8 '12 at 10:07
  • i'm trying to find the session of the script from another session. Say I have a running script and from SQLDeveloper i want to find that session to monitor it. – VCarstein Aug 8 '12 at 11:47
2

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)   
  • yes, i use something like sqlplus -s $dwh_connect @$path_sql/check_link_db.sql in the .ksh so this should do the trick. thanks! – VCarstein Aug 8 '12 at 12:48
3

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');
  • thanks, this is more helpful in the long run, a quicker method to identify scripts, but unfortunately this requires editing of scripts which are not mine. thanks! – VCarstein Aug 8 '12 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.