Q
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


This was last published in August 2003

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close