执行中SQL确认

select /* Check Runing SQL */

p.SPID UNIX_PID

, s.SID

, s.LOGON_TIME

, s.STATUS

, s.module "batch code"

, o2.name "PLSQL Name"

, to_char((sysdate - s.SQL_EXEC_TART) * 24 * 60 ,'FM990.0') MIN

, s.SQL_ID

, s.SQL_CHILD_NUMBER CN

, (

       select p.PLAN_HASH_VALUE

           from v$sql_plan p

       where p.SQL_ID = s.SQL_ID

            and p.CHIL_NUMBER = s.SQL_CHILD_NUMBER

            and rownum =1

) phv

, (select count(1) from dba_hist_sqlstat h where h.plan_hash_value = (

select p.PLAN_HASH_VALUE

 from v$sql_plan p

 where p.SQL_ID = s.SQL_ID

   and p.CHIL_NUMBER = s.SQL_CHILD_NUMBER

  and rownum =1 ) and h.sql_id = s.sql_id

) times

,t.SQL_TEXT_RUNNING_SQL

, decode(s.TIME_SINCE_LAST_WAIT_MICRO, 0, s.EVENT, NULL)  WAIT_EVENT

, decode(s.TIME_SINCE_LAST_WAIT_MICRO, 0, trunc(s.WAIT_TIME_MICRO / 1000), NULL) "(ms)"

, decode(s.TIME_SINCE_LAST_WAIT_MICRO, 0, o.NAME, NULL) LAST_WAIT_OBJ

 , LPAD(TO_CHAR(p.PGA_MAX_MEM /1024/1024, 'FM999,990'), 7) MEM_MB

, ( select /*+ leading(SO@"SEL$5" S@"SEL$7" TS@"SEL$5") */ LPAD(TO_CHAR(SUM(u.BLOCKS * c.BLOCK_SIZE) / 1024 / 1024, 'FM999,990'), 7)

from v$sort_usage u

, dba_tablespaces c

 where u.SESSION_ADDR = s.SADDR

 and u.TABLESPACE = c.TABLESPACE_NAME ) TEMP_MB

, s.SCHEMANAME

-- , s.ACTION

-- , s.CLIENT_IDENTIFIER CLIENT_INFO

-- , s.MODULE

, s.PROGRAM

, s.MACHINE

, s.OSUSER

, 'select * from table(dbms_xplan.display_cursor(''' || s.SQL_ID || ''', ' || s.SQL_CHILD_NUMBER || ',''ADVANCED''));' DISPLAY_XPLAN

, 'alter system kill session ''' || s.sid || ',' || s.serial# || '''; --' || p.SPID DDL_FOR_KILL_SESSION

, 'begin dbms_monitor.session_trace_enable(' || s.sid || ',' || s.serial# || ', true, false); end; --' || p.SPID DDL_FOR_ENABLE_TRACE

 from v$sqltext t

, v$session s

, v$process p

 , sys.obj$ o

, sys.obj$ o2

 -- ,dba_objects

where 1=1

 and t.ADDRESS = s.SQL_ADDRESS

 and s.STATUS in ('ACTIVE', 'KILLED')

 and s.PADDR = p.ADDR

 and t.SQL_ID = s.SQL_ID

 and t.PIECE = 0

 and s.ROW_WAIT_OBJ# = o.OBJ# (+)

and s.plsql_entry_object_id = o2.obj# (+)

 and t.SQL_TEXT not like '%Check Running SQL%'

and s.EVENT# not in (409, 348) -- AQ待ち除く

-- and s.program <> 'sqlplus@HN322SV2 (TNS V1-V3)'

and s.USERNAME != 'SYS'

--and s.MACHINE = 'MW71VX589AXRI2'

--and module = 'DBMS_SCHEDULER'

order by s.LOGON_TIME

,p.SPID