[ACCEPTED]-How to see the actual Oracle SQL statement that is being executed-oracle

On the data dictionary side there are a 20 lot of tools you can use to such as Schema Spy

To look 19 at what queries are running look at views 18 sys.v_$sql and sys.v_$sqltext. You will 17 also need access to sys.all_users

One thing 16 to note that queries that use parameters 15 will show up once with entries like

and TABLETYPE=’:b16’

while 14 others that dont will show up multiple times 13 such as:

and TABLETYPE=’MT’

An example of these tables in action 12 is the following SQL to find the top 20 11 diskread hogs. You could change this by 10 removing the WHERE rownum <= 20 and maybe add ORDER BY module. You often 9 find the module will give you a bog clue 8 as to what software is running the query 7 (eg: "TOAD 9.0.1.8", "JDBC 6 Thin Client", "runcbl@somebox 5 (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 <= 20;

Note that if the query 4 is long .. you will have to query v_$sqltext. This 3 stores the whole query. You will have to 2 look up the ADDRESS and HASH_VALUE and pick 1 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
;

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

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

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

Good night.

I think the V$SQLAREA table contains what you're 1 looking for (see columns SQL_TEXT and SQL_FULLTEXT).

Yep, that's definitely possible. The v$sql 6 views contain that info. Something like 5 this piece of code should point you in the right direction. I 4 haven't tried that specific piece of code 3 myself - nowhere near an Oracle DB right 2 now.

[Edit] Damn two other answers already. Must 1 type faster next time ;-)

-- i use something like this, with concepts 2 and some code stolen from asktom.
-- suggestions 1 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 < 30
) SQL_TEXT
FROM sess si;

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