Do you remember the film Up where the dogs were always distracted as soon as anyone mentioned squirrels ?
Well, there I was, continuing my journey through the wonderful world of PL/SQL Unit Tests when suddenly, SQLcl !
Yes, Oracle have just released the first production version of SQLcl.
Since I first looked at an Early Adopter version of SQLcl there have been several enhancements. One of these, the REPEAT command, has the potential to implement functionality akin to the good old *nix tail -f command for Oracle Database tables.
It turns out that you may also be able to do something similar in SQL*Plus…
REPEAT or…what she said !
Having installed the latest and greatest PRODUCTION release of sqlcl (version 4.2.0 Production at the time of writing), I can find out about this newfangled REPEAT command from the help….
To see REPEAT in action, let’s run this query :
Now let’s re-execute it ten times with a delay of 1 second between each execution…
Well that’s nice but how about something a bit more practical ?
OK…
Tailing a table
Consider that we have a (very) simple application logging table…
…and a PL/SQL job which writes to it…
If we run this job in one session we can use another to monitor it’s progress.
The pre-12c version of this monitoring query might be something like…
…the 12c version would be rather more concise, what with the LIMIT clause being available, but that’s for another time.
For now, the output would be something like…
We can now re-issue this command and see each new line being added to the table in the same way as if we were using tail -f on a file…
At first glance, this looks to be more like a tail -ffab ( follow for a bit). However, digging a bit deeper…
Yes, the maximum number of repeats is the maximum size of a PLS_INTEGER ( 32-bit integer). Even with a very small interval specified, this means that you can, in effect, replicate tail-f by specifying a huge number of iterations.
As with tail -f, you can also cancel a REPEAT by issuing a CTRL+C. SQLcl should pick up and process the cancel during the next iteration of the REPEAT “loop”.
Therefore, this may take a while to process, depending on what you have set your repeat interval to.
Time for an old favourite…
Tailing the alert log in SQL – look no external tables !
Now, there was a time when tailing the alert log was the main method of monitoring a running instance. Later on, there was a time when you could do this from within the database, but only with the help of an External Table on the alert log.
Since 11g however, it has been possible to do this by using the X$DBGALERTEXT fixed table.
As with fixed tables generally however, getting access to it can be a bit fiddly.
We could still do this, but we’d need to be connected as SYS. Rather than going through all of that, I’m just going to follow Benedikt Nahlovsky’s fine example and expose this fixed table in the usual way….
Now, as a user with the DBA role, I can run this in SQLcl to see the last 10 entries in the alert log…
…followed by this to “tail -f” the alert log….
Kris Rice, one of the team behind SQLcl (among other things) has another demonstration of what is possible with this command.
Let’s face it, you can’t do that with SQL*Plus…can you ?
REPEAT…just like Mother used to make
You may be surprised to learn that, there are circumstances in which you can implement a pretty good imitation REPEAT/tail -f functionality in good old SQL*Plus.
If you’re running on a *nix (or similar) environment then this code from Katsumi is a bit of an eye-opener…
Save this as plus_tailf.sql and then run it, and the output looks like this…
As you’d expect, provided your host is running an appropriate OS, this will work in SQLcl as well as in SQL*Plus.
Better still, there is a way of invoking this functionality without having to type that eval statement every time.
First of all, we’re going to create a script which accepts the name of a file holding a SQL script to run, the number of times to execute the script, and the interval between each execution ….
We’re going to save this in the SQLPATH directory as tailf.sql.
My SQLPATH is defined in my .bashrc as :
We also need to save our query to get the current time into a file called time.sql. This can be in any directory.
We can now invoke it from SQL*Plus ( or SQLcl) simply by running…
Why SQL*Plus still matters
You may think that this is all rather a lot of effort to implement this functionality in SQL*Plus, when it’s readily available in SQLcl anyway. After all, why not just switch to using SQLcl, which provides a rather more concise (not to mention platform independent) solution ?
It’s worth remembering that Oracle client software doesn’t just run on Developers’ workstations, it runs on Application and Database Servers as well.
The fact of the matter, particularly in large organisations, is that there is considerable inertia to overcome in getting new software onto production servers.
Just think for a moment, it’s now over 4 years since Oracle 12c was officially released. However, many (most ?) Oracle databases are still running on older versions of the database.
As well as that, SQLcl has a dependency on Java so either getting the correct version, or just getting Java installed in the first place, is an additional challenge when it comes to navigating SQLcl through whatever Change Management procedures may be in place.
So, whilst SQLcl will undoubtedly become ubiquitous over time, SQL*Plus won’t be disappearing just yet.
Filed under: Oracle, SQL Tagged: alert.log, eval, host, mkfifo, repeat, SQL*Plus, sqlcl, X$DBGALERTEXT