Take the 2-minute tour ×
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's 100% free, no registration required.

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

share|improve this question
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. –  jbuhagiar Aug 8 '12 at 11:47
add comment

2 Answers

up vote 2 down vote accepted

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)   
share|improve this answer
    
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! –  jbuhagiar Aug 8 '12 at 12:48
add comment

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');
share|improve this answer
    
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! –  jbuhagiar Aug 8 '12 at 12:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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