I have an Oracle PL/SQL procedure monitoring_5_min and as far as I know it must be envoked every 5 minutes by some external interface (possibly a web service) or some scheduled job. Are there any Oracle system methods to find out what invokes the procedure? Maybe some system views? I tried to select from v$sql but it seems that it only include SQL calls but not PL/SQL.

CodePudding user response：

You can query v$session to get all the details:

select * from v$session where sid = sys_context('userenv','sid')

Also look at OWA_UTIL.WHO_CALLED_ME

CodePudding user response：

Alex, the most likely ways to run a task on a schedule in Oracle is either via cron (if on UNIX) or via DBMS_SCHEDULER. You can look at both options, and query DBA_DEPENDENCIES for references to the stored procedure providing the the task is not actually using anonymous PL/SQL.

While the SP is being ran you can find the calling session via V$ACCESS then via V$SESSION get the Oracle username, OS username, machinne the request is from, and program being invoked which can help identify the source.

The suggested use of dba_hist_active_sess_history requires that your site is licensed for the extra-cost EM Diagnostic Pack license.