Tracing all SQL statements from Compiere

For those who ventured too far - just hope they know their way back :)

Moderator: neilg

Tracing all SQL statements from Compiere

Postby neilg » Wed Nov 23, 2005 9:20 pm

Originally from a post by Marco Lombardo; modified and embelished by me.

For Oracle 9i.

if anyone has easier ways .etc please post them Disclaimer: I am not an oracle expert

You can also get SQL statements from sessions by looking at the OEMAPP sessions. Its much quicker than this but I guess it is a different application.

How to get all the SQL statements via oracle from Compiere

- Go to your 'user_dump_dest' directory. You can find this parameter value under the oracle enterprise manager (oemapp), Instance, Parameters.

- Remove all the files in this folder.

- let Compiere connect.

- look at v$session and identify the session created get SERIAL# and SID column

(Connect as sys):

Code: Select all
select * from v$session


Enable tracing as follows:

Code: Select all
DECLARE
        l_sid NUMBER;
        l_serial NUMBER;
    BEGIN
        SELECT sid, serial#
        INTO l_sid, l_serial
        FROM v$session
        WHERE username = 'User1';

        dbms_system.set_sql_trace_in_session (l_sid, l_serial, TRUE);
    END;


Or

Code: Select all
begin
dbms_system.set_sql_trace_in_session ((l_sid, l_serial, true);
dbms_system.set_sql_trace_in_session (13, 6, true);
dbms_system.set_sql_trace_in_session (10, 86, true);
dbms_system.set_sql_trace_in_session (14, 6, true);
dbms_system.set_sql_trace_in_session (15, 77, true);
end;


- Do your compiere actions
- disable trace in the same session. Mandatory!
e.g.
Code: Select all
begin
dbms_system.set_sql_trace_in_session ((l_sid, l_serial, false);
dbms_system.set_sql_trace_in_session (13, 6,false);
dbms_system.set_sql_trace_in_session (10, 86,false);
dbms_system.set_sql_trace_in_session (14, 6,false);
dbms_system.set_sql_trace_in_session (15, 77, false);
end;


- go to you user_dump_dest directory and look for newly generated file.
- transform them to readable form:
tkprof <input trace file name> <output readable file>
e.g.
Code: Select all
tkprof erp_ora_20680.trc 20680.txt
neilg
Regular
 
Posts: 141
Joined: Thu Jun 16, 2005 1:58 pm

Return to Strictly Technical

Who is online

Users browsing this forum: No registered users and 2 guests