0

We use a closed source application but the built-in reporting tools aren't great so we've started using a seperate database reporting application to generate our reports.

I need to get a report copied across to our new reporting application.

I want to run the report in our old application and monitor what queries it is sending to the Oracle database so that I can copy those queries across to our new reporting application.

How can I do this?

6
  • 2
    V$SQL and/or V$SQL_TEXT? Google around that and you should find more - but make sure nothing else is running or else you run the risk of confusing yourself. Why can't you simple point the reporting app at the new database? Do you not have the code for your app? And if you don't have the code, how do you know that it's producing the correct results anyway? Take a look here. But be careful that you don't miss any short statements.
    – Vérace
    Mar 21 '16 at 14:49
  • To me this sounds like a bit open ended question. If you have a closed source app, how exactly do you propose you will 'copy those reporting queries to new reporting app' ?
    – Raj
    Mar 21 '16 at 20:01
  • @Raj that's the point in the question. I need to see what queries the closed source application is running via the Oracle database. Mar 21 '16 at 20:02
  • You do not specify your db version, but you could potentially audit sql, run a periodic job to capture all sqls run by that user, if you have license, use AWR (but you may miss some SQLs), if applicable check v$sql_monitor (only in 12c and query must be running longer than 6sec or use parallel etc). There are multiple ways, you just have to figure out the easiest one for you in your environment.
    – Raj
    Mar 21 '16 at 20:05
  • @Raj is there a tool like tail -f but for the v$ queries so I can have that open and run the report and see the queries as they're running, rather than having to keep doing select's and hoping I catch it? Mar 21 '16 at 20:07
2

Start the old application, and log in. Find your session in the database (let's say: sid=12, serial#=3456), and enable SQL tracing for that session, for example:

begin
  dbms_monitor.session_trace_enable
  (
    session_id => 12,
    serial_num => 3456,
    binds => true
  )
end;
/

There are several ways of enabling SQL tracing, it is just one of them. It may be useful to enable capturing bind values (as above), so you can match your report input parameter values to the query bind variables.

You can find the name of the trace file as:

select
  p.tracefile
from
  v$process p 
  join v$session s on (p.addr = s.paddr)
where
  s.sid = 12
;

You can start running your report from the application, and follow the contents of the trace file (even tail -f, but it will not be human-friendly). After you have finished, disable tracing:

begin
  dbms_monitor.session_trace_disable
  (
    session_id => 12,
    serial_num => 3456
  )
end;
/

You can read the raw trace file (if you are patient enough and interested in more details), but if you want only the query texts, you can use tkprof on the trace file (e.g: orcl_ora_6789.trc):

tkprof orcl_ora_6789.trc orcl_ora_6789.tk sys=no aggregate=no

With aggregate=no added, tkprof will not aggregate the SQL statements and it will generate an output where the order in which the statements were executed is preserved. Without that, you can aggregate multiple executions of the same SQL and sort them, a typical use case would be to sort by elapsed time, so you the output starts with the longest running queries:

tkprof orcl_ora_6789.trc orcl_ora_6789.tk sys=no sort=prsela,exeela,fchela
0

You can use the v$sessions table to see all the active queries. If you are using PL/SQL Developer, then Tools > Sessions and then Active Sessions.

You can find all the active sessions in the database and if you view the 'SQL Text' for these sessions, you will get the query running in the backend.

In addition to this, you can refer the columns: Machine, Program, Logon time, Schema-name etc to pinpoint the exact session

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.