| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Question with INTO clause of SQLPLUS
A copy of this was sent to Joe Condle <condle_at_mars-systems.com>
(if that email address didn't require changing)
On Sat, 10 Apr 1999 09:38:27 -0400, you wrote:
[snip]
>
>My goal is to use the time variable as a start time and then at some
>later time
>execute
> select sysdate-time into elapsed_time: from dual.
>If the elapsed_time is greater than five minutes then I want to send
>mail to the DBA's warning ofa possible process that needs tuning.
Below is a script that shows how to do this (even does the email on unix).
REM here is the stuff you want to time
begin
dbms_lock.sleep( 10 );
end;
/
set feedback off
set serveroutput on
spool tmp.sql
declare
l_stop number default dbms_utility.get_time; l_threshold_in_secs number default 5; begin
if ( (l_stop-:start)/100 > l_threshold_in_secs ) then
dbms_output.put_line( 'host echo Took Too long | mail tkyte' );
end if;
end;
/
spool off
set feedback on
@tmp.sql
It is setup to do it in seconds and i have it set at 5 seconds. to do 5 minutes, just default the threshold to 5*60 instead of 5.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |