Re: How to capture SQL_TEXT of queries?

Dragon Fly (dfly_at_infinet.com) wrote:
: In short, I need to set up kind of a trigger
: on some of the tables so that it captures the
: text of the queries run against those tables.
: The problem is, the "standard"
: triggers can be set up only for UPDATE OR INSERT
: while I need a trigger for SELECT statement.
: Anybody has an idea what is the good way of implementing
: such kind of thing?
 

: Thanks,
: Sergei

--

Hi,

it depends how complex your requirements are but the AUDIT command can be 
used for this. You can use it to audit select, update, insert etc in general
or you could use it to audit specific SQL-statements. But this requires you to
be able to define the SQL-statements in advance. If your requirements 
is to capture any ad_hoc SQL-statement or the number of sql-statements are 
large then auditing will not be suitable.

You could set the init.ora parameter sql_trace=true. Then the database will
dump any sql-statement to trace files. This will give you all statements
and not only for specific tables. Afterward you would have to analyze the
trace files yourself tkprof and a script to find queries for those
tables that interest you.

Creating triggers in the dictionary will not work. This is because
the information in the dictionary are contained in views which you cannot 
create triggers on. These views are based on 'fixed tables' (X$ tables) and you
cannot create triggers on those either.


Rgds
Steinar Heggelund

----------------------------------------------------------------------------
I'm employed in the Norwegian consulting company Opus One AS. 
I have 7 years experience with Oracle products, mainly the database. 
We are a small company which offers consulting services in design,
implementation and tuning of databases and applications based on Oracle.