2

I need to capture Oracle stored procedures calls (with parameters) to trace an application (which uses JDBC to connect to the DB). I need something like sp_trace_setevent for Rpc:Completed event in MS SQL SERVER.

I do not have access to this application, but have mostly all rights in the database. I would like to stay in PL/SQL (and Oracle SQL Developer 3.2.20).

I have tried:

  1. Oracle SQL Developer UI "Tools"/ "RealTime SQL Monitoring" and "Tools"/ "Sessions" instruments but can't understand how to enabling accumulating information instead of capturing moment snapshot.

  2. exploring v$sql - it seems there are no sp calls.

  3. v$sqlarea differences (Oracle: is there a tool to trace queries, like Profiler for sql server? , mdj3884 reply) - there I am able to find my test call, but without parameters...

  4. Suggestion from Tom's article : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:767025833873. Particularly, it is looping through v$sqltext_with_newlines, but I can't understand what is a script result. Something more like moment snapshot; isn't it? But then why they call it tracking?

  5. use DBMS_APPLICATION_INFO - as I understand using this I can add custom info to V$SESSION and V$SESSION_LONGOPS - it can be useful for monitoring tasks but I can't imagine how it can be used for accumulating information about sp calls and theirs parameters.

  6. use DBMS_MONITOR for enabling tracing into file. but I can't find option to enable tracing only sp call events, also it requires access to the server files.

  7. DBMS_PROFILER - collects as I understand by default it collects only statistics (min, max time); there should be possibility to add custom information to plsql_profiler_runs but I can't find this table (when DBMS_PROFILER is in place).

What to see next? What I have missed?

P.S. If the only one way is to change SP body (those SP which need to be traced) then what is the quickest and safest way to log sp parameters from sp body in Oracle? It could be logging to custom table, but may be I could choose between generating another types of events (that are not rollbacked, something like SQL Server custom trace events)?

Roman Pokrovskij
  • 9,449
  • 21
  • 87
  • 142
  • I don't know for sure, but DBMS_AUDIT_MGMT may allow you to do this. I know it can be used to trap table changes, so maybe it can also trap PL/SQL stuff. Anyone else know? – OldProgrammer Apr 01 '14 at 15:56
  • The definition of AUDIT_TRAIL (object of DBMS_AUDIT_MGMT) from ( http://docs.oracle.com/cd/E11882_01/network.112/e36292/auditing.htm#DBSEG342 ) say that it is a report of DDL operation happend. Also I can't find a word about auditing sp calls. :( It is so hard to read Oracle docs that I am never sure 100% in my conclusions. – Roman Pokrovskij Apr 01 '14 at 22:54

2 Answers2

1

It would be easy to add some custom functionality to do this (see below for most of what is required) or you could use sqltrace or the enterprise manager reports and search through them:

create package p_audit as 
 type t_param_type is table of varchar2(50) index by binary_integer;

 procedure p_audit (p_procedure varchar2, l_param_type t_param_type);
end;

create table audit_table (procedure_name varchar2(50), parameters varchar2(500))

create or replace package body p_audit is

procedure p_audit (p_procedure varchar2, l_param_type t_param_type) is
  pragma autonomous_transaction;  
begin
  insert into audit_table values (p_procedure,l_param_type(1));
  commit;
end;

end p_audit;

declare
  l_param_type p_audit.t_param_type;
begin
  l_param_type(1):='parameter 1';
  p_audit.p_audit('test procedure',l_param_type);
end;
kayakpim
  • 985
  • 2
  • 8
  • 28
  • This is a good idea - to make a gereneralization basing on custing to varchar2... One day I will use it. Now I have made the p_audit and audit_table procedure generator without type generalisation. – Roman Pokrovskij Apr 12 '14 at 13:43
1

I used DBMS_AUDIT trail to determine what kind of procedures/functions/packages used when a client side application executed. I would really recommend you to use it once, it really helps, but problem is that you cannot analise deeper in a package hierarchy, its function/procedure called (calls), but only its usage. If you want to know dependency of the package you can use ALL_DEPENDENCIES. It can be helpful.

ddsultan
  • 2,027
  • 1
  • 19
  • 19