How to see the actual Oracle SQL statement that is being executed

Question

I m using a custom-built inhouse application that generates a standard set of reports on a weekly basis  I have no access to the source code of the application  and everyone tells me there is no documentation available for the Oracle database schema   Aargh    I ve been asked to define the specs for a variant of an existing report  e g   apply additional filters to constrain the data set  and modify the layout slightly   This sounds simple enough in principle  but is difficult without any existing documentation    It s my understanding that the logs can t help me because the report only queries the database  it does not actually insert  delete  or update database values  so there is nothing to log  is this correct     So my question is this  is there a tool or utility  Oracle or otherwise  that I can use to see the actual SQL statement that is being executed while the report generation job is still running  I figure  if I can see what tables are actually being accessed to produce the existing report  I ll have a very good starting point for exploring the schema and determining the correct SQL to use for my own report

User · Answer

Sorry for the short answer but it is late. Google "oracle event 10046 sql trace". It would be best to trace an individual session because figuring which SQL belongs to which session from v$sql is no easy if it is shared sql and being used by multiple users.

If you want to impress your Oracle DBA friends, learn how to set an oracle trace with event 10046, interpret the meaning of the wait events and find the top cpu consumers.

Quest had a free product that allowed you to capture the SQL as it went out from the client side but not sure if it works with your product/version of Oracle. Google "quest oracle sql monitor" for this.

Good night.

User · Answer

Yep  that s definitely possible   The v sql views contain that info   Something like this piece of code should point you in the right direction   I haven t tried that specific piece of code myself - nowhere near an Oracle DB right now    Edit  Damn two other answers already   Must type faster next time  -

User · Answer

--  i use something like this  with concepts and some code stolen from asktom   --  suggestions for improvements are welcome   WITH  sess AS       SELECT       FROM V SESSION     WHERE USERNAME   USER      ORDER BY SID   SELECT si SID         si LOCKWAIT         si OSUSER         si PROGRAM         si LOGON TIME         si STATUS                      SELECT ROUND USED UBLK 8 1024 1              FROM V TRANSACTION                  sess            WHERE sess TADDR   V TRANSACTION ADDR            AND sess SID   si SID            rollback remaining                        SELECT  MAX DECODE PIECE   0 SQL TEXT NULL                         MAX DECODE PIECE   1 SQL TEXT NULL                         MAX DECODE PIECE   2 SQL TEXT NULL                         MAX DECODE PIECE   3 SQL TEXT NULL                         MAX DECODE PIECE   4 SQL TEXT NULL                         MAX DECODE PIECE   5 SQL TEXT NULL                         MAX DECODE PIECE   6 SQL TEXT NULL                         MAX DECODE PIECE   7 SQL TEXT NULL                         MAX DECODE PIECE   8 SQL TEXT NULL                         MAX DECODE PIECE   9 SQL TEXT NULL                         MAX DECODE PIECE  10 SQL TEXT NULL                         MAX DECODE PIECE  11 SQL TEXT NULL                         MAX DECODE PIECE  12 SQL TEXT NULL                         MAX DECODE PIECE  13 SQL TEXT NULL                         MAX DECODE PIECE  14 SQL TEXT NULL                         MAX DECODE PIECE  15 SQL TEXT NULL                         MAX DECODE PIECE  16 SQL TEXT NULL                         MAX DECODE PIECE  17 SQL TEXT NULL                         MAX DECODE PIECE  18 SQL TEXT NULL                         MAX DECODE PIECE  19 SQL TEXT NULL                         MAX DECODE PIECE  20 SQL TEXT NULL                         MAX DECODE PIECE  21 SQL TEXT NULL                         MAX DECODE PIECE  22 SQL TEXT NULL                         MAX DECODE PIECE  23 SQL TEXT NULL                         MAX DECODE PIECE  24 SQL TEXT NULL                         MAX DECODE PIECE  25 SQL TEXT NULL                         MAX DECODE PIECE  26 SQL TEXT NULL                         MAX DECODE PIECE  27 SQL TEXT NULL                         MAX DECODE PIECE  28 SQL TEXT NULL                         MAX DECODE PIECE  29 SQL TEXT NULL                    FROM V SQLTEXT WITH NEWLINES             WHERE ADDRESS   SI SQL ADDRESS AND              PIECE  lt  30           SQL TEXT FROM sess  si

User · Answer

On the data dictionary side there are a lot of tools you can use to such as Schema Spy  To look at what queries are running look at views sys v  sql and sys v  sqltext  You will also need access to sys all users  One thing to note that queries that use parameters will show up once with entries like   and TABLETYPE     b16      while others that dont will show up multiple times such as   and TABLETYPE    MT      An example of these tables in action is the following SQL to find the top 20 diskread hogs  You could change this by removing the WHERE rownum  lt   20 and maybe add ORDER BY module  You often find the module will give you a bog clue as to what software is running the query  eg   TOAD 9 0 1 8    JDBC Thin Client    runcbl somebox  TNS V1-V3   etc   SELECT   module    sql text    username    disk reads per exec    buffer gets    disk reads    parse calls    sorts    executions    rows processed    hit ratio    first load time    sharable mem    persistent mem    runtime mem    cpu time    elapsed time    address    hash value  FROM     SELECT    module      sql text       u username       round  s disk reads decode s executions 0 1  s executions   2   disk reads per exec      s disk reads       s buffer gets       s parse calls       s sorts       s executions       s rows processed       100 - round 100    s disk reads greatest s buffer gets 1  2  hit ratio      s first load time       sharable mem       persistent mem       runtime mem      cpu time      elapsed time      address      hash value    FROM    sys v  sql s      sys all users u    WHERE    s parsing user id u user id     and UPPER u username  not in   SYS   SYSTEM      ORDER BY    4 desc   WHERE  rownum  lt   20    Note that if the query is long    you will have to query v  sqltext  This stores the whole query  You will have to look up the ADDRESS and HASH VALUE and pick up all the pieces  Eg   SELECT    FROM  sys v  sqltext WHERE  address    C0000000372B3C28   and hash value    1272580459  ORDER BY   address  hash value  command type  piece

User · Answer

I had  have  a similar problem in a Java application  I wrote a JDBC driver wrapper around the Oracle driver so all output is sent to a log file

User · Answer

I think the V SQLAREA table contains what you re looking for  see columns SQL TEXT and SQL FULLTEXT

[sql] How to see the actual Oracle SQL statement that is being executed

Examples related to sql

Examples related to oracle