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, '')

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

• Dozens more answers to tough Oracle questions from Karen Morton are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our applications, PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.