REPEAT after me, it’s easy to monitor with Oracle SQLcl!

I’ve talked about the REPEAT command before, in fact it was one of my earlier posts on SQLcl, all the way back in 2015!

But I’ve found it’s necessary to tell a story multiple times, in multiple ways in order to really get the word out. And this is a HANDY feature.

What it does

REPEAT X Y 

Whatever the last thing (a SQL statement, or even sa SQL script!) you executed…it executes again, X number of times, with a Y second delay.

The delay Y max is 120 seconds.

And in between executions, it refreshes the screen. Which makes it work quite nicely as a custom ‘monitor.’

Now let’s put this in terms that are interesting to YOU.

What it is, that you do here

You load data. All the time. And you like to keep an eye on how that’s going. So let’s setup a scenario where we’re going to load some CSV to an Oracle table.

And while that data load is going, I want a ‘monitor’ on my CLI to show me what’s happening.

And the cool part is, we can use the REPEAT command to run both of those things.

1. The Data Loader

We need a table.

CREATE TABLE HR.BANK_TRANSFERS
 (
  TXN_ID NUMBER(4),
  SRC_ACCT_ID NUMBER(5),
  DST_ACCT_ID NUMBER(5),
  DESCRIPTION VARCHAR2(26),
  AMOUNT NUMBER(6)
 )
;

We need some data.

TXN_ID,SRC_ACCT_ID,DST_ACCT_ID,DESCRIPTION,AMOUNT
1,171,831,transfer,8948
2,172,305,transfer,6784
3,172,292,transfer,1006
4,172,294,transfer,6342
...

2. The “Monitor”

I have a script, “repeat-multiple.sql” that looks like this –

WITH metrics AS (
   SELECT statistic# AS id
        , name
        , VALUE
     FROM v$sysstat
    WHERE class = 1
      AND ( name LIKE 'user %' )
)
SELECT STAT_6.value AS USER_COMMITS
     , STAT_7.value AS USER_ROLLBACKS
     , STAT_8.value AS USER_CALLS
     , STAT_12.value AS CUMULATIVE_USER_LOGONS
     , STAT_13.value AS CUMULATIVE_USER_LOGOUTS
     , STAT_26.value AS USER_IO_WAIT_TIME
  FROM (
   SELECT MIN(VALUE) AS VALUE
     FROM metrics
    WHERE name = 'user commits'
) STAT_6
     , (
   SELECT MIN(VALUE) AS VALUE
     FROM metrics
    WHERE name = 'user rollbacks'
) STAT_7
     , (
   SELECT MIN(VALUE) AS VALUE
     FROM metrics
    WHERE name = 'user calls'
) STAT_8
     , (
   SELECT MIN(VALUE) AS VALUE
     FROM metrics
    WHERE name = 'user logons cumulative'
) STAT_12
     , (
   SELECT MIN(VALUE) AS VALUE
     FROM metrics
    WHERE name = 'user logouts cumulative'
) STAT_13
     , (
   SELECT MIN(VALUE) AS VALUE
     FROM metrics
    WHERE name = 'user I/O wait time'
) STAT_26;
 
 
SELECT systimestamp;
 
SELECT COUNT(*) NUM_BANK_TRANSFERS FROM bank_transfers;

Show me some session stats, show me what time it is, and show me how many rows are in my BANK_TRANSFERS table.

Let’s go!

Terminal one…run the load command. Not once, but 15x, with a 1 second delay.

You DO KNOW about the LOAD command, right?

I simply run this once

“load bank_transfers BANK_TRANSFERS.csv”

And then I follow that with the repeat command, “repeat 15 1”

Terminal two, watching what’s happening over in terminal one.

I run the @script and then follow that with another “repeat 10 1”

What did we learn today?

The REPEAT and LOAD commands are quite handy, AND can be used together! Also, if I go more than a few days without blogging, I start to get itchy. I should probably see a doctor about that.