Vishal desai’s Oracle Blog

July 1, 2013

Refresh SQL output every N seconds

Filed under: Tools, Troubleshooting — vishaldesai @ 3:27 pm

In Linux there is watch command to repeat Unix Commands or Shell-Scripts every N seconds. To monitor output of SQL script at regular interval of time from sqlplus you can use refresh.sql.

Example 1: To monitor output of session_longops every 5 seconds you can run following command:

SQL> @refresh.sql session_longops 5 5

SID             % Complete Time Now        ELAPSED_SECONDS MESSAGE
--------------- ---------- --------------- --------------- ---------------------------------------------------------------
1227.1457,@1         99.99 130701 10:10:17               1 RMAN: aggregate input: backup 33: 7831 out of 7832 Blocks done

Example 2: To monitor output of Instance wait every 5 seconds you can run following command:

SQL> @refresh.sql swact 5 5

  INST_ID     SID STATE   EVENT                                          SEQ# SEC_IN_WAIT P1              P2            P3                 P1TRANSL
--------- ------- ------- ---------------------------------------- ---------- ----------- ------------------ ------------------ ------------------ --
        1    1179 WAITING PX Deq: Execute Reply                           320           3 sleeptime/senderid passes= 2          0
                                                                                          = 200

        1     961 WAITING Streams AQ: waiting for messages in the       23225           2 queue id= 12603    process#=          wait time= 5
                                                                                                             0x0000000C910276C0

        1    1227 WAITING control file sequential read                  45873           0 file#= 0           block#= 1          blocks= 1
        1      52 WAITING direct path read                               4197           0 file number= 156   first dba= 730752  block cnt= 128
        1      99 WAITING direct path read                               4225           0 file number= 156   first dba= 2571008 block cnt= 128
        1      25 WAITING direct path read                               3933           0 file number= 157   first dba= 1747200 block cnt= 128
        1     123 WAITING direct path read                               4167           0 file number= 158   first dba= 621824  block cnt= 128
        1      74 WAITING direct path read                               4180           0 file number= 158   first dba= 2348160 block cnt= 128
        1     146 WAITING direct path read                               4297           0 file number= 159   first dba= 2502658 block cnt= 126
        1     314 WORKING On CPU / runqueue                              4393           0
        1       1 WORKING On CPU / runqueue                              4123           0
        1     749 WORKING On CPU / runqueue                               582           0

Script:

--Usage: refresh.sql "sql script name" interval sample
set feed off
set head off
set echo off
set term off
set linesize 120
set verify off
spool refresh_1.sql
set feedback off
set feed off
set serveroutput on 
select cmd from (
select '@' || '&1'  as cmd from dual
union all
select 'exec dbms_lock.sleep(&2);' as cmd from dual
union all
select 'clear scr' as cmd from dual
) , (select rownum from dual connect by level <=&3) ;
spool off
set term on
set serveroutput on
set head on
clear scr
@refresh_1.sql
Advertisements

2 Comments »

  1. Cool idea mate, I was using something like http://jonathanlewis.wordpress.com/2011/03/29/repetition/ but your model is better (wish oracle comes up with more flexible privilege option of dbms_lock.sleep

    Comment by coskan — April 21, 2014 @ 9:43 am

  2. Thanks Coskan.

    On unix, dbms_lock.sleep can be replaced with host sleep.

    On windows, I tried using timeout command but sqlplus session hangs after timeout command.

    ping -w will work but will display extra output.
    select ‘host ping 1.1.1.1 -n 1 -w &2’ || ‘000’ as cmd from dual

    Comment by vishaldesai — April 21, 2014 @ 5:44 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: