Oracle: tracing SQL statements

Question

I'm maintaining an home-brew web-based help-desk solution and we are experiencing an erroneous change in a db field under some circumstances. The code involved is quite frankly a mess, mixing different approaches to database access (the worst and most dangerous one being building sql strings through concatenation). A full-text search of the problematic field in the entire solution didn't help (the field only appeared in selects and in a single legitimate insert) so I can't even find the offending code.

Do you know a fast way to enable sql tracing either in Asp.Net or in Oracle (8) itself? (Is it possible to intercept sql commands through the OracleConnection object or something like that? That would make it possible to get a stack trace and find out the buggy code).

I've done some googling and I've found this one: ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY; but I'd like to know which is the better way to solve the problem (and maybe build up a simple sql logging mechanism without rewriting the application).

Answer 1

To trace a specific session in Oracle 8 use the PROCEDURE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION

TKPROF And Oracle Trace (8i)

Might be of interest:

• Tracing SQL in Oracle Database 10g

• SQL Trace

• Using SQL Trace and TKPROF

• Using Application Tracing Tools

Answer 2

Enabling tracing is the way to go, but if you're having trouble getting that working, try to see if you can find reference to the offending column in the sql_text column of SYS.v$sql. Assuming that the statement has run recently (and that the statement that did the deed isn't so long that the column you're looking for has been truncated - Oracle 8 didn't store the full text), you should be able to locate it there. The only other case where the SQL wouldn't show up there is if the change were occuring via a trigger or stored proc, in which case you should be able to locate the column in the TEXT column of the ALL_SOURCES table.