SET LINES 32767 LONG 65536 FEEDBACK OFF TRIMSPOOL ON HEADING ON PAGES 24

UNDEFINE search_string
UNDEFINE sql_hash_value
UNDEFINE sql_address
UNDEFINE sql_child_number
UNDEFINE sql_id
UNDEFINE sid

CLEAR column

BREAK ON SID SKIP PAGE

COLUMN username FORMAT A10 WRAP
COLUMN prog_event FORMAT A35 WRAP
COLUMN run_time FORMAT A10 JUSTIFY RIGHT
COLUMN sid FORMAT A4 NEW_VALUE sid
COLUMN status FORMAT A10
COLUMN sql_fulltext FORMAT a32767

ACCEPT search_string PROMPT "Search for: "
column st format a1 trunc

SELECT  to_char(s.sid) AS sid
,       s.username || chr(10) || s.osuser AS username
,       s.status || chr(10) || 'PID:' || p.spid AS status
,       lpad(
                to_char(
                        trunc(24*(sysdate-s.logon_time))
                ) ||
                to_char(
                        trunc(sysdate) + (sysdate-s.logon_time)
                ,       ':MI:SS'
                )
        , 10, ' ') AS run_time
,       s.program ||  chr(10) || s.event AS prog_event
,       cursor (
                SELECT  sql_fulltext 
                FROM    v$sql t
                WHERE   t.hash_value    = s.sql_hash_value
                AND     t.address       = s.sql_address
                AND     t.child_number  = s.sql_child_number
                AND     t.sql_id        = s.sql_id
                UNION ALL
                SELECT  chr(10) || to_clob(max(message))
                FROM    v$session_longops t
                WHERE   t.sql_hash_value    = s.sql_hash_value
                AND     t.sql_address       = s.sql_address
                AND     t.sql_id        = s.sql_id
                AND     t.sid           = s.sid
                AND     t.totalwork     > t.sofar
        ) AS st
FROM    v$session s
JOIN    v$process p ON (p.addr = s.paddr)
WHERE   s.username IS NOT NULL
AND     audsid != sys_context('USERENV','SESSIONID')
AND     upper(
                s.osuser || '|' ||
                s.program || '|' ||
                s.event || '|' ||
                s.sid || '|' ||
                s.username || '|' ||
                p.spid
        ) LIKE upper('%&search_string.%')
ORDER BY
        sid
/

CLEAR BREAK
SET LINES 80 FEEDBACK ON
prompt