Gudu Software: Provides Oracle tools to facilitate your daily DBA tasks

Home

Products

Downloads

Registration

Articles

Scripts

Links

About Us

Free Newsletter

Capture sql in web based application

     In N tier structure it is really hard to track user session specially in a web enviroment, All users connect to database using the same application , so all requests are going to database on behalf of one oracle user. then how can you track actions of one ENDUSER(application user).

     In the v$session, there are serval columns you can use to distinguish ENDUSER such as Module,Action and Client info. for example, you can use

   dbms_application_info.set_client_info('client info')

to set client info accroding to user's ip or loginname in your application,
then it is easy to find out what sql statement was execute by this client use following script:

  select s.sid, s.username, s.program, oc.sql_text
  from v$session s, v$open_cursor oc
  where s.saddr=oc.saddr and s.sid=oc.sid
  and s.client_info='client_info';

if you want to capture all sql in real time, Oracle Session Manager can help you do it.