trace the sql

[maswien]

I want trace the queries the application issues on the backend database, I want see those queries saved in a log for me to investigate. How can do that?

 

[LKBrwnDBA]

ALTER SESSION SET SQL_TRACE=TRUE;

The use tkprof utility to check the trace file.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[maswien]

I want the trace is turned on not only for the current session but the global. Is that possible?

 

[LKBrwnDBA]

Yes, use ALTER SYSTEM.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[pete91z]

but be careful, this will generate lots of logs!

 

[LKBrwnDBA]

Another solution is to create a "login" trigger on the schema you wish to audit and enable tracing for each connection to that schema only:

CREATE TRIGGER AUD_SCHEMA_X AFTER LOGON
  ON SCHEMA MySchema 
  BEGIN
    EXECUTE IMMEDIATE "ALTER SESSION SET SQL_TRACE=TRUE";
    ...etc...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb