REM Script:       last_sql.sql
REM Title:        shows the last executed statement per session
REM Compatible:   Oracle 7.x
REM Usage:        run as system(DBA)
REM                
REM Parameters:   0
REM History:
REM                23-Sep-1998 JKrueger Created
REM Modificatins : 22-Sept-99 JKrueger : execution counter




set head     on
set echo     off
set verify   off
set feedback off
set linesize 80
set pagesize 1000
set tab      off

-- the format of the columns
col "SID"                        format 999
col "Program"                    format A12
col "Last (or current) query"    format A64
col "Executions"                 format 999999999

-- set a break
BREAK ON SID ON Program

-- to find the query text
select ses.sid       "SID", 
ses.status "Status", 
area.executions "Executions",
      sql.sql_text  "Last (or current) query"     
from  v$session ses,
      v$sqltext sql,
      v$sqlarea area
where ses.sql_address = sql.address
and ses.sql_address = area.address
order by ses.status, sid, piece ;


