<p>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!)</p>

<p>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. </p>

<p>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?).</p>

<p>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.</p>

[{'answer_id': 55912, 'author': 'David Crow', 'author_id': 2783, 'author_profile': 'https://Stackoverflow.com/users/2783', 'pm_score': 2, 'selected': False, 'text': '<p>I think the <a href="http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2129.htm" rel="nofollow noreferrer">V$SQLAREA</a> table contains what you\'re looking for (see columns <strong>SQL_TEXT</strong> and <strong>SQL_FULLTEXT</strong>).</p>\n'}, {'answer_id': 55914, 'author': 'Mark Nold', 'author_id': 4134, 'author_profile': 'https://Stackoverflow.com/users/4134', 'pm_score': 6, 'selected': True, 'text': '<p>On the data dictionary side there are a lot of tools you can use to such as <a href="http://schemaspy.sourceforge.net/" rel="noreferrer">Schema Spy</a></p>\n\n<p>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</p>\n\n<p>One thing to note that queries that use parameters will show up once with entries like </p>\n\n<pre><code>and TABLETYPE=’:b16’\n</code></pre>\n\n<p>while others that dont will show up multiple times such as:</p>\n\n<pre><code>and TABLETYPE=’MT’\n</code></pre>\n\n<p>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 <strong>WHERE rownum &lt;= 20</strong> and maybe add <strong>ORDER BY module</strong>. 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)</p>\n\n<pre><code>SELECT \n module, \n sql_text, \n username, \n disk_reads_per_exec, \n buffer_gets, \n disk_reads, \n parse_calls, \n sorts, \n executions, \n rows_processed, \n hit_ratio, \n first_load_time, \n sharable_mem, \n persistent_mem, \n runtime_mem, \n cpu_time, \n elapsed_time, \n address, \n hash_value \nFROM \n  (SELECT\n   module, \n   sql_text , \n   u.username , \n   round((s.disk_reads/decode(s.executions,0,1, s.executions)),2)  disk_reads_per_exec, \n   s.disk_reads , \n   s.buffer_gets , \n   s.parse_calls , \n   s.sorts , \n   s.executions , \n   s.rows_processed , \n   100 - round(100 *  s.disk_reads/greatest(s.buffer_gets,1),2) hit_ratio, \n   s.first_load_time , \n   sharable_mem , \n   persistent_mem , \n   runtime_mem, \n   cpu_time, \n   elapsed_time, \n   address, \n   hash_value \n  FROM\n   sys.v_$sql s, \n   sys.all_users u \n  WHERE\n   s.parsing_user_id=u.user_id \n   and UPPER(u.username) not in (\'SYS\',\'SYSTEM\') \n  ORDER BY\n   4 desc) \nWHERE\n rownum &lt;= 20;\n</code></pre>\n\n<p>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:</p>\n\n<pre><code>SELECT\n *\nFROM\n sys.v_$sqltext\nWHERE\n address = \'C0000000372B3C28\'\n and hash_value = \'1272580459\'\nORDER BY \n address, hash_value, command_type, piece\n;\n</code></pre>\n'}, {'answer_id': 55915, 'author': 'Hobo', 'author_id': 1714, 'author_profile': 'https://Stackoverflow.com/users/1714', 'pm_score': 2, 'selected': False, 'text': '<p>Yep, that\'s definitely possible.  The v$sql views contain that info.  Something like <a href="http://www.oracle.com/technology/oramag/code/tips2005/100305.html" rel="nofollow noreferrer">this piece of code</a> should point you in the right direction.  I haven\'t tried that specific piece of code myself - nowhere near an Oracle DB right now.</p>\n\n<p>[Edit] Damn two other answers already.  Must type faster next time ;-)</p>\n'}, {'answer_id': 55920, 'author': 'Ethan Post', 'author_id': 4527, 'author_profile': 'https://Stackoverflow.com/users/4527', 'pm_score': 3, 'selected': False, 'text': '<p>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.</p>\n\n<p>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. </p>\n\n<p>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.</p>\n\n<p>Good night.</p>\n'}, {'answer_id': 73179, 'author': 'metadave', 'author_id': 7237, 'author_profile': 'https://Stackoverflow.com/users/7237', 'pm_score': 0, 'selected': False, 'text': '<p>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. </p>\n'}, {'answer_id': 74558, 'author': 'EvilTeach', 'author_id': 7734, 'author_profile': 'https://Stackoverflow.com/users/7734', 'pm_score': 1, 'selected': False, 'text': '<p>--  i use something like this, with concepts and some code stolen from asktom. <BR>\n--  suggestions for improvements are welcome <BR>\n<BLOCKQUOTE>\nWITH <BR>\nsess AS<BR>\n(<BR>\n    SELECT *<BR>\n    FROM V$SESSION<BR>\n    WHERE USERNAME = USER <BR>\n    ORDER BY SID<BR>\n)<BR>\nSELECT si.SID,<BR>\n       si.LOCKWAIT,<BR>\n       si.OSUSER,<BR>\n       si.PROGRAM,<BR>\n       si.LOGON_TIME,<BR>\n       si.STATUS,<BR>\n       (<BR>\n           SELECT ROUND(USED_UBLK*8/1024,1) <BR>\n           FROM V$TRANSACTION,<BR>\n                sess<BR>\n           WHERE sess.TADDR = V$TRANSACTION.ADDR<BR>\n           AND sess.SID = si.SID<BR>\n<BR>\n        ) rollback_remaining,<BR>\n<BR>\n        (<BR>\n           SELECT (MAX(DECODE(PIECE,  0,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE,  1,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE,  2,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE,  3,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE,  4,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE,  5,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE,  6,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE,  7,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE,  8,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE,  9,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 10,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 11,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 12,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 13,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 14,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 15,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 16,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 17,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 18,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 19,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 20,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 21,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 22,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 23,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 24,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 25,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 26,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 27,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 28,SQL_TEXT,NULL)) ||<BR>\n                   MAX(DECODE(PIECE, 29,SQL_TEXT,NULL)))    <BR>\n            FROM V$SQLTEXT_WITH_NEWLINES<BR>\n            WHERE ADDRESS = SI.SQL_ADDRESS AND <BR>\n            PIECE &lt; 30<BR>\n        ) SQL_TEXT<BR>\nFROM sess  si;<BR>\n</BLOCKQUOTE></p>\n'}]
['https://Stackoverflow.com/questions/55899', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/480/']