sql.sql_id.sql

<- Scripts

-- sql.sql_id.sql
-- Purpose: Show SQL run for given SQL_ID.
-- ---------------------------------------
set linesize 240
set pagesize 9999
set verify off

COL elapse              FORMAT 999999
COL username            FORMAT a15
COL module              FORMAT a20
COL sid                 FORMAT 999999
COL SESSION_SERIAL#     FORMAT 999999
COL sql_plan_hash_value FORMAT 9999999999 HEAD "PHV"
COL action              FORMAT a20

-- Prompt
clear screen
PROMPT +-------------------------------+
PROMPT | Show SQL run for Given SQL_ID |
PROMPT +-------------------------------+
PROMPT
ACCEPT vSQL_ID PROMPT "Enter SQL_ID: "
ACCEPT vDays   PROMPT "For last n Days [5]: " DEFAULT 5
PROMPT

-- Process
--set verify on
SELECT to_char(sql_exec_start, 'YYYY-MM-DD HH24:MI') as "STARTED", 
       round(elapsed_time/60000000,2) as "ELAPSE", 
       round(cpu_time/1000000/60, 2)  as "CPU", 
       sid, 
       session_serial# as "SERIAL#", 
       sql_id, 
	   sql_plan_hash_value, 
       username, 
       substr(module,1,15) as "MODULE", 
       '...' || substr(program,-20) as "PROGRAM", 
	   substr(status,1,15) as "STATUS",
       substr(action,1,15) as "ACTION"  
FROM   v$sql_monitor
WHERE  sql_exec_start >= sysdate - &vDays 
AND    sql_id = '&vSQL_ID'
ORDER BY sql_exec_start DESC;

-- End
set verify off
prompt
prompt "*** Process Ended ***"
prompt