Doc

Name: SQL_ACT.sql

Author: Mahnho Han

Get the SQL that is running this point

#

SPOOL SQL_ACT

SET PAGESIZE 60

SET LINESIZE 80

SET HEADING OFF

COLUMN PROCESS NEW_VALUE PIDVAR NOPRINT

COLUMN SID NEW_VALUE SIDVAR NOPRINT

COLUMN DISK_READ NEW_VALUE DSKVAR NOPRINT

COLUMN SVRNAME NEW_VALUE SVRVAR NOPRINT

TTITLE LEFT 'PID: ' PIDVAR COL20 'SID: ' SIDVAR COL 40

'SVRNAME: ' SVRVARD: ' COL 80 'DISK_READ: ' DSKVAR SKIP 2

BREAK ON SID SKIP PAGE

BTITLE OFF

SELECT SID,SVRNAME,PROCESS,

ROUND(DISK_READS/NVL(SA.EXECUTIONS,0),0) DISK_READ,

SQL_TEXT

FROM V$SQLTEXT ST,

V$SQLAREA SA,

(SELECT SE.PROCESS,SE.SID,SERIAL#,STATUS,USERNAME,

SVRNAME,SQL_HASH_VALUE

FROM V$SESSION SE, SYSTEM.HMH_PS H

WHERE STATUS = 'ACTIVE'

AND USERNAME != ' '

AND SE.PROCESS = H.PROCES(+)) SI

WHERE SI.SQL_HASH_VALUE = ST.HASH_VALUE

AND SI.SQL_HASH_VALUE = SA.HANS_VALUE

ORDER BY SID,PIECE

/

SPOOL OFF