Home » SQL & PL/SQL » SQL & PL/SQL » View running SQL
View running SQL [message #189235] Wed, 23 August 2006 14:43 Go to next message
Messages: 27
Registered: May 2005
Junior Member
I had a procedure that called many different procedures and functions. Everything was related, so I bundled it into a package. Before doing that, I could monitor all running SQL using TOAD. Now that that it's all in a package. TOAD just shows pkg_xxx.proc_xxx, not select abc... Is there a switch in TOAD to show SQL in running packages, or is there an Oracle table that I can query to get the running SQL?

Re: View running SQL [message #189295 is a reply to message #189235] Thu, 24 August 2006 01:39 Go to previous messageGo to next message
Messages: 7062
Registered: December 2001
Senior Member
If it is in a package, it is ran on the server. So a client tool like toad (that comes with a nifty utility called "SQLMonitor") will not be able to grab it. If you want to monitor server side SQL, you'd go for a trace.
Let me explain. Assume I have the following package (it is rubbish, but it has some SQL in it):
  Procedure testme;
END testpck;

  PROCEDURE testme
    v_loc   NUMBER;
    FOR drec IN (SELECT *
                 FROM   departments)
      FOR erec IN (SELECT *
                   FROM   employees
                   WHERE  department_id = drec.department_id)
        DBMS_OUTPUT.put_line ('Do nothing ;)');
      END LOOP;

    SELECT location_id
    INTO   v_loc
    FROM   locations
    WHERE  ROWNUM = 1;
  END testme;
END testpck;

I can start a SQL*Plus session to trace the SQL performed in my session:
SQL> alter session set sql_trace=TRUE
  2  /

Session altered.

SQL> Begin
  2    testpck.testme;
  3  End;
  4  /

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=FALSE;

Session altered.

SQL> exit
Now I need to look in the user_dump_dest where the trace file is generated (a directory on the server). Ask your DBA or check with the following query:
SELECT value
FROM   v$parameter
WHERE  NAME = 'user_dump_dest'

On my local db it returned "C:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP". I checked the directory and found the file "xe_ora_3664.trc". I renamed it to xe_test.trc and moved it to my C:\useful directory. Then I started tkprof to transform this unreadable file to a text file. I'm not interested in SYS SQL so I turned SYS off in the output:
C:\Useful>tkprof xe_test.trc xe_test.txt sys=no

TKPROF: Release - Production on Do Aug 24 08:12:17 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Now I can look at the xe_test.txt file for all the SQL performed in my session.

Is that what you are looking for?

PS: I've attached the xe_test.txt file.

  • Attachment: xe_test.txt
    (Size: 7.38KB, Downloaded 175 times)
Re: View running SQL [message #189411 is a reply to message #189295] Thu, 24 August 2006 09:04 Go to previous message
Messages: 27
Registered: May 2005
Junior Member
Thanks for the help! I think this will help, although I'm told there is a size limit here on the log size.

What I was doing prior to packaging all the procedures together, and there's a lot of them (50+), was following the process using TOAD's session browser. That was convienient because if a statement was taking a long time to run, I could see what it was doing and do an explain plan on the spot. Apparently this can't be done for procedures within a package. Kinda sucks. I'll give the trace a try.

Thanks again!
Previous Topic: Problem in Query
Next Topic: Store a text file into CLOB column
Goto Forum:

Current Time: Thu Jan 19 07:39:35 CST 2017

Total time taken to generate the page: 0.63048 seconds