How can you show the SQL that is currently executing on an oracle db?
Extra information that would be useful would include user, session id etc.
Most of the info is available in v$session ... and the SQL Text can be grabbed from v$sql or v$sqltext_with_newlines...
Here is a query that I often use that formats In-flight SQL, longest running at top.
-- In Flight SQL
SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
SID,
MACHINE,
REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT
FROM V$SESSION SES,
V$SQLtext_with_newlines SQL
where SES.STATUS = 'ACTIVE'
and SES.USERNAME is not null
and SES.SQL_ADDRESS = SQL.ADDRESS
and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
and Ses.AUDSID <> userenv('SESSIONID')
order by runt desc, 1,sql.piece;
Good answer found here.
Execute the following sql:
select x.sid
,x.serial#
,x.username
,x.sql_id
,x.sql_child_number
,optimizer_mode
,hash_value
,address
,sql_text
from v$sqlarea sqlarea
,v$session x
where x.sql_hash_value = sqlarea.hash_value
and x.sql_address = sqlarea.address
and x.username is not null;
If the output is unreadable, change the LINESIZE (take from here):
SET LINESIZE 20000
If the sql above does not work, you might need to log in as sysdba:
sqlplus '/as sysdba'