Sometimes you may need to know what SQL a program is executing in Oracle. This SQL query can be used.
SELECT s.sid,
s.serial#,
s.username,
s.machine,
s.program,
s.status,
s.sql_id,
q.sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
AND s.username IS NOT NULL;However, if there are many SQL statements being executed, and they all complete very quickly, running the previous SQL statement won’t capture the SQL issued by the program. This is because the SQL statement may have already completed by the time you execute it. In this case, you can use the following SQL statement, which will capture the SQL statements executed within the previous minute.
SELECT sql_id,
parsing_schema_name,
module,
last_active_time,
executions,
sql_text
FROM v$sql
WHERE last_active_time > SYSDATE - 1/1440 -- Last minute
ORDER BY last_active_time DESC;