Problem solve Get help with specific problems with your technologies, process and projects.

Tracing queries in specific schema or database

Can we trace the queries that are executed in a particular schema or database?

You can enable tracing for an entire instance by setting the init.ora parameter SQL_TRACE=TRUE. However, this is very dangerous and can run away from you very quickly by eating up large amounts of disk space to write the trace files. Personally, I don't think there's many (if any) reasons why you'd ever want to do that. Tracing an individual session is much better and less consuming of disk space and other resources to do.

To trace an individual's session, you'll need their SID and SERIAL# (from v$session) and then you execute the following procedure from SQL*Plus:

execute sys.dbms_system.set_ev(sid, serial#, 10046, 12, '')
That will turn tracing on for a single session only. Then you can have that session do everything you want (run through the application, etc.) and then review that resulting trace file (or files) much more effectively than tracing every single thing that's going on in the database.

You can capture recently executed queries (particularly problem queries) by checking v$sqlarea as well.

The bottom line is that you need to make sure you know exactly what and why you are tracing in order to be effective.

For More Information

Dig Deeper on Oracle and SQL