A package procedure is scheduled to execute at a give time (12:00:00 A.M.).
How does one determine the terminiation time of a procedure?
Note: Estimated time of completion is between 1:00 or 2:00 A.M.
This oracle installation works with ERP software system. Only the
vendor has rights to modify package code. This limits the one to creating a trigger or examining a trace of the procedure.
Could the use of trigger be used, if so how? Is there an oracle
dynamic peformance view that shows the information needed?
Now one could examine a trace session to determine the when the
process ends, but sleep sound like a better idea.
1) use the total_time column in dba_jobs. BUT this is cumulative time i.e. the time taken for running the job since the very first time so if it takes 10 minutes then the second time the job run total_time will be 20 mins.
So you could have another job which copies the dba_jobs table to another table so you can subtract the total_time from the previous days total_time to get the time spent today.
2) write sysdate to your own log table at the end of your stored proc.