--

-- ses_wide.sql is basically the same thing as ses.sql but

-- uses listagg() to concatenate the sql pieces so that

-- the statement returns one row per session.

--

select

ses.sid,

ses.serial#,

ses.username,

ses.osuser,

ses.logon_time,

listagg(sql.sql_text, '') within group (order by sql.piece) sql_text,

(sysdate - ses.sql_exec_start) * 60 * 60 * 24 sql_running_since

from

v$session ses left join

v$sqltext sql on ses.sql_address = sql.address and ses.sql_hash_value = sql.hash_value

where

ses.sid != sys_context('USERENV','SID') and

ses.osuser != 'oracle'

group by

ses.sid,

ses.serial#,

ses.username,

ses.osuser,

ses.logon_time,

(sysdate - ses.sql_exec_start) * 60 * 60 * 24

order by

(sysdate - ses.sql_exec_start) * 60 * 60 * 24 desc nulls last;