In previous blog posts I’ve covered some of the useful new commands available in SQLcl.
One extremely useful enhancement is the ability to rerun (repeat) the last command, which can be used to monitor changes in another window while you perform work in your main window.
For example, let’s say – as a DBA – I want to monitor how many users are logged into the database.
Firstly lets logging as SYS (yes this is a local database – obviously use a less privileged account in production).
bash-3.2$ ./sql sys@localhost:11521:xe as sysdba SQLcl: Release 4.1.0 Release Candidate on Tue Jun 17 18:42:12 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Password? (**********?) ****** Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL>
Now lets run our command –
SQL> select username,
2 count(*)
3 from v$session
4 group by username;
USERNAME COUNT(*)
------------------------------ ----------
22
APEX_PUBLIC_USER 1
SYS 1
As you can tell, I’m running APEX in my DB (hence the single APEX_PUBLIC_USER session).
Now let’s say we want to continuously run that command, you just use the REPEAT command –
SQL> help repeat repeat <iterations> <sleep> Repeats the current sql in the buffer the specified times with sleep intervals Maximum sleep is 120s
So, let’s say I want to run it 5 times, with an interval of 1 second –
SQL> repeat 5 1
This will run the previous command 5 times (with 1 second between each run), and you should see output similar to –
Running 5 of 5 @ 18:51:11.900 with a delay of 1s
USERNAME COUNT(*)
22
APEX_PUBLIC_USER 1
SYS 1
I’v e used this many times to monitor APEX instances which I suspect are getting a lot of web requests, it’s also useful to check things like V$SQL during tuning sessions. The ability to be able to re-run a command at a scheduled interval is such a simple feature and yet invaluable (I’ve lost count of the number of times I’ve manually done that with SQLPlus scripts)
