repeat.sql


Print this article  Send the URL to a friend

— ==========================================================================

— 
— Copyright (C) RoughSea Ltd, 2006
— http://www.roughsea.com
— 
— This program is free software; you can redistribute it and/or modify
— it under the terms of the GNU General Public License as published by
— the Free Software Foundation; either version 2 of the License, or
— any later version.
— 
— This program is distributed in the hope that it will be useful,
— but WITHOUT ANY WARRANTY; without even the implied warranty of
— MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
— GNU General Public License for more details.
— 
— You should have received a copy of the GNU General Public License
— along with this program; if not, write to the Free Software
— Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
— 
— ==========================================================================
— 
— One sometimes wishes to monitor something in the database at regular
— interval. It is pretty difficult and boring to do with SQL*Plus.
— If the query that you want to repeat is in a SQL file (say q.sql)
— you can repeat it say 20 times at a 5 second interval by running:
— @repeat q.sql 20
— 
— Two prerequisites:
— o Oracle >= 9.x
— o Execute rights on the DBMS_LOCK package
— 
— Usage :
— @repeat <script>
— 
— Note that a log file is created.
—  store set myenv replace set echo off set autotrace off set linesize 120 set trimspool on set pagesize 99 set timing off set verify off set recsep off set feedback off set pagesize 0 set termout off spool repeat_&1..sql select ’exec dbms_lock.sleep(5);’ || chr(10) || ’@&1’ from (select rownum rn from dual connect by 1 = 1 and rownum <= case when &2 <= 100 then &2 else ceil(&2/20) end) / select ’@repeat_&1’ from dual where &2 > 100 / spool off set termout on whenever sqlerror continue spool repeat_&1..log set pagesize 99 @&1 set pagesize 0 @repeat_&1 spool off @myenv