SQLcl – do REPEAT yourself

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)

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