How to find your SQL statements inside Oracle...

We recently saw a forum question on how to identify your own SQL inside the database:

Can a servlet or jsp page that does someting in database(dml) be written in such a manner that i can identify from the database the source of the sql?

Since it's a useful thing to know I'll expand on the answer we gave him below:

We've made the assumption that the goal is to see what SQL statement is being issued now, which rules out TKPROF, or Enterprise Manager.

Ordinary login privileges won't allow you to see what's going on. In order to be able to find your SQL you must first have the right to "see" the sessions inside the database. To do this you'll need access to V$SESSION .

The next step is to find the database session(s) involved. If you know the SQL involved you can issue a query that lists all the sessions that are executing it:

select  s.sid, s.serial#, s.machine, s.osuser, a.sql_text

from   v$session s

,   v$sql a

where s.sql_id = a.sql_id

and   a.sql_text like '%UPDATE foo%';

'sid' and 'serial#' collectively identify the session. "machine" and "osuser" should identify the client computer and user. "sql_text" will contain the first 1000 characters of the SQL statement being issued. If you issue a lot of similar statements or want to find statements issued by a given session you can use hints to make your statement of interest findable:

SELECT /* This is freds SQL */ * FROM emp;



Note: On May 17th 2010 OrindaBuild was renamed to JDBCWizard