I have a piece of java code provided by the vendor, as it is. For me it is a black box, as I cannot see the code inside but I know it connects to an Oracle database and executes some SQL/PLSQL. Is there any possibility to see what exactly is execute, maybe using AWR? If yes, can anyone provide me the steps to do that, please?
Thank you.
Yes, you can, though this requires that you either be a DBA or at least have the select any dictionary privilege.
Certain method: enable 10046 tracing on the session. If it's a quick-lived session or performs the action immediately after logon, you may have to use a logon trigger to set this event in the session (e.g. EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 12''') before they perform the activity in question. Then simply examine the trace file to see every SQL executed. There is a lot of material out there you can google that gives instructions for how to set a 10046 trace via various means (e.g. sql_trace parameter, dbms_system.set_ev, etc...). But all will require DBA level privileges both to enable and to pick up the trace from the database host, so if you aren't a DBA, ask your DBA to do this for you.
Hit-or-miss method: immediately after the activity in question, query gv$sql (requires select any dictionary privilege) to see if you can find a recently parsed cursor that was parsed by the schema/user (use parsing_schema_name) in question. PL/SQL anonymous blocks and calls to stored procedures all require cursors, though they tend to get flushed out of the shared pool pretty quickly.
Hit-or-miss method: if the activity in question takes more than a couple seconds to execute, you should be able to find it in gv$active_session_history, which records what every active session is doing once per second. Again, you should do this immediately after the activity as after an hour or two this data starts disappearing and you get only 1/10th of it retained in the dba_hist_active_sess_history view. This will give you more handles to identify your session, and then you can use the sql_id to get the cursor from gv$sql or from dba_hist_sqltext.