Re: How to find out sql statements in a program

Hi,

  You can use ORACLE trace feature, to generate a trace-file (in server) and then read this file to get whatever sql statement your program used to achive its goal.

  To do this, you can open a SQL*Plus session as DBA and issue this command:

  SQL> exec dbms_system.set_sql_trace_in_session (sid, serial#,

       TRUE);   where, sid and serial# are both parameters u can get from

  SQL> SELECT USERNAME, MACHINE, SID, SERIAL#, AUDSID        FROM V$SESSION;   (identify your session by username x machine pair, and use    the other values)

  Then do your work (use your program) and when its done stop   tracing, with:

  SQL> exec dbms_system.set_sql_trace_in_session (sid, serial#,

       FALSE);   Now get your trace file (that should be in   $ORACLE_HOME/rdbms/log or another configurated   place) and try to read it.

  If your trace-file isn't in default place, use this   statement to find out where it is:

  SELECT NAME, VALUE
  FROM V$PARAMETER
  WHERE NAME = 'user_dump_dest';

  The name of trace-file is something like   "ORA<SESSIONID>.TRC", where sessionid is   V$SESSION.AUDSID value... (in NT the value   in trace-file is expressed in hex)

  Hope it helps

  Alexandre Junqueira

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!