Getting explaing plan of the Currrent running in the session

Getting explaing plan of the Currrent running in the session

Run sid.sql and then run plans.sql <SID> to check the explain plan
--------------------------------------sid.sql ----------------------------------------
#List currently running session IDs
SET LINES 80 LONG 65536
CLEAR column
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
ACCEPT search_string PROMPT "Search for: "
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
FROM    v$session s
JOIN    v$process p ON (p.addr = s.paddr)
WHERE   s.username <> 'DBSNMP'
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
/
Only the SELECT statement above is necessary if running from a GUI tool. The other commands and the chr(10) concatenation simply format the output for SQL*Plus.
<EOF>--------------------------------------sid.sql----------------------------------------
--------------------------------------plans.sql----------------------------------------
#List the EXPLAIN PLAN for a currently running session
SELECT  p.plan_table_output
FROM    v$session s
,       table(dbms_xplan.display_cursor(s.sql_id, s.sql_child_number)) p
where   s.sid = &1
<EOF>--------------------------------------plans.sql----------------------------------------