How to determine an Oracle query without access to source code?

Question

We have a system with an Oracle backend to which we have access (though possibly not administrative access) and a front end to which we do not have the source code. The database is quite large and not easily understood - we have no documentation. I'm also not particularly knowledgable about Oracle in general.

One aspect of the front end queries the database for a particular set of data and displays it. We have a need to determine what query is being made so that we can replicate and automate it without the front end (e.g. by generating a csv file periodically).

What methods would you use to determine the SQL required to retrieve this set of data?

Currently I'm leaning towards the use of an EeePC, Wireshark and a hub (installing Wireshark on the client machines may not be possible), but I'm curious to hear any other ideas and whether anyone can think of any pitfalls with this particular approach.

Answer 1

Start with querying Oracle system views like V$SQL, v$sqlarea and v$sqltext.

Answer 2

Wireshark is indeed a good idea, it has Oracle support and nicely displays the whole conversation.

A packet sniffer like Wireshark is especially interesting if you don't have admin' access to the database server but you have access to the network (for instance because there is port mirroring on the Ethernet switch).

Answer 3

Which version of Oracle? If it is 10+ and if you have administrative access (sysdba), then you can relatively easy find executed queries through Oracle enterprise manager.

For older versions, you'll need access to views that tuinstoel mentioned in his answer.

Same data you can get through TOAD for oracle which is quite capable piece of software, but expensive.

Answer 4

Clearly there are many methods. The one that I find easiest is:

(1) Connect to the database as SYS or SYSTEM

(2) Query V$SESSION to identify the database session you are interested in. Record the SID and SERIAL# values.

(3) Execute the following commands to activate tracing for the session:

exec sys.dbms_system.set_bool_param_in_session( *sid*, *serial#*, 'timed_statistics', true )
exec sys.dbms_system.set_int_param_in_session( *sid*, *serial#*, 'max_dump_file_size', 2000000000 )
exec sys.dbms_system.set_ev( *sid*, *serial#*, 10046, 5, '' )

(4) Perform some actions in the client app

(5) Either terminate the database session (e.g. by closing the client) or deactivate tracing ( exec sys.dbms_system.set_ev( sid, serial#, 10046, 0, '' ) )

(6) Locate the udump folder on the database server. There will be a trace file for the database session showing the statements executed and the bind values used in each execution.

This method does not require any access to the client machine, which could be a benefit. It does require access to the database server, which may be problematic if you're not the DBA and they don't let you onto the machine. Also, identifying the proper session to trace can be difficult if you have many clients or if the client application opens more than one session.

Answer 5

I have used these instructions successfully several times: http://www.orafaq.com/wiki/SQL_Trace#Tracing_a_SQL_session

Answer 6

A quick and dirty way to do this, if you can catch the SQL statement(s) in the act, is to run this in SQL*Plus:-

set verify off lines 140 head on pagesize 300

column sql_text format a65
column username format a12
column osuser format a15

break on username on sid on osuser

select S.USERNAME, s.sid, s.osuser,sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value 
order by s.sid,t.piece
/

You need access those v$ views for this to work. Generally that means connecting as system.

Answer 7

"though possibly not administrative access". Someone should have administrative access, probably whoever is responsible for backups. At the very least, I expect you'd have a user with root/Administrator access to the machine on which the oracle database is running. Administrator should be able to login with a "SQLPLUS / AS SYSDBA" syntax which will give full access (which can be quite dangerous). root could 'su' to the oracle user and do the same.

If you really can't get admin access then as an alternative to wireshark, if your front-end connects to the database through an Oracle client, look for the file sqlnet.ora. You can set trace_level_client, trace_file_client and trace_directory_client and get it to log the Oracle network traffic between the client and database server.

However it is possible that the client will call a stored procedure and retrieve the data as output parameters or a ref cursor, which means you may not see the query being executed through that mechanism. If so, you will need admin access to the db server, and trace as per Dave Costa's answer