Home » SQL & PL/SQL » SQL & PL/SQL » trace procedure
trace procedure [message #208765] Tue, 12 December 2006 03:05 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!,
can i check if a procedure is currently running? i mean if is already started thru a cron, how can i check if it is?
Re: trace procedure [message #208769 is a reply to message #208765] Tue, 12 December 2006 03:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you've run a procedure from DBMS_JOB or DBMS_SCHEDULER, then you can check the view DBMS_JOBS_RUNNING.

If it's a Cron job, then you'd need to have a look at the table V$session, and look at the logon_time, comparing that to the time the Cron job started.
Re: trace procedure [message #208946 is a reply to message #208769] Tue, 12 December 2006 12:18 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
A good approach is to get the procedure to identify itself and then look for that signature in v$session.

dbms_application_info.set_module( 'my-module', 'my-action'); 
dbms_application_info.set_client_info( 'whatever' ); 


select count(*) from v$session
where MODULE = 'my-module'
--and ACTION =  'my-action'
--and CLIENT_INFO = 'whatever'
Re: trace procedure [message #208950 is a reply to message #208946] Tue, 12 December 2006 12:42 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Obviously you need to reset the values at the end of the procedure else the signature will remain in v$session. It's usually a non-issue if the session terminates after running the proc.
Re: trace procedure [message #208998 is a reply to message #208769] Tue, 12 December 2006 22:01 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!,
thanks for the replies, dbms_application_info is good but the procedure isnt written with it, i've check v$session and yes i've got the logon_time, thanks again, also managed to track it through v$sqltext.

Previous Topic: I'm trying to get the time difference from 2 column
Next Topic: an problem in TKPROF
Goto Forum:
  


Current Time: Tue Dec 06 02:25:57 CST 2016

Total time taken to generate the page: 0.12839 seconds