Ask the Expert

Tool in Oracle 10g to keep track of execution of queries

How can I keep track of the execution of stored procedures, functions and queries in Oracle 10g, as I used to do using SQL Server Profiler? Is there any tool in 10g for this purpose? Thanks and regards.

    Requires Free Membership to View

The Oracle SQL Trace facility is what you use here. In your session, issue the following:

ALTER SESSION SET sql_trace=TRUE;

Then run your application. At the end, set SQL_TRACE to FALSE. This will create a trace file in the USER_DUMP_DEST directory.

If you want to start a trace in another user's session, you'll have to determine that session's SID and SERIAL# values as can be seen in V$SESSION. Then sign on to SQL*Plus as SYSTEM and issue the following:

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(sid,serial);

To stop the trace, issue the following:

EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(sid,serial);
Details on the SQL Trace package can be found in the Oracle documentation.

This was first published in May 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: