Chasing your tail – with SQL*Plus and SQLcl

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….

help repeat

repeat <iterations> <sleep>
	Repeats the current sql in the buffer the specified times with sleep intervals
 	Maximum sleep is 120s

To see REPEAT in action, let’s run this query :

select to_char(sysdate, 'HH24:MI:SS') as the_time
from dual
/

THE_TIME
--------
08:27:47

Now let’s re-execute it ten times with a delay of 1 second between each execution…

 

repeat_time

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…

create table logs
(
	time_stamp timestamp,
	message varchar2(100)
)
/

…and a PL/SQL job which writes to it…

begin
	for i in 1..10000 loop
		dbms_lock.sleep(2);
			insert into logs(time_stamp, message)
			values( systimestamp, 'Iteration '||i);
		commit;
	end loop;
	insert into logs(time_stamp, message)
	values( systimestamp, 'Run completed.');
	commit;
end;
/

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…

with tail_logs as
(
    select time_stamp, message
    from logs
    order by time_stamp desc
)
    select to_char(time_stamp, 'HH24:MI:SS') as log_time, message
    from tail_logs
    where rownum <= 5
    order by time_stamp
/

…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…


LOG_TIME MESSAGE
-------- ----------------------------------------------------------------------------------------------------
13:06:23 Iteration 46
13:06:25 Iteration 47
13:06:27 Iteration 48
13:06:29 Iteration 49
13:06:31 Iteration 50                                                                                        

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…

tail_table

At first glance, this looks to be more like a tail -ffab ( follow for a bit). However, digging a bit deeper…

-- make sure there's something in the buffer
select user from dual;
-- now cause repeat to generate the Usage error message
repeat

Usage: REPEAT  <iterations> <seconds>
	 SQL is the sql from the current buffer
	 Maximum sleep is 120s
	 Maximum repeats are 2,147,483,647

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….

-- as SYS as SYSDBA...
create or replace view alert_log_vw as select * from x$dbgalertext
/

create public synonym alert_log for alert_log_vw
/

grant select on alert_log to dba
/

Now, as a user with the DBA role, I can run this in SQLcl to see the last 10 entries in the alert log…

with log_lines as
(
	select originating_timestamp,
		to_char(originating_timestamp, 'Day Mon DD HH24:MI:SS YYYY') as message_ts,
		message_text
	from alert_log
	order by originating_timestamp desc
)
select message_ts, message_text as file_line
from log_lines
where rownum <= 10
order by originating_timestamp
/

…followed by this to “tail -f” the alert log….

repeat 86400 1

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

ho mkfifo /tmp/myfifo.sql
select to_char(systimestamp, 'HH24:MI:SS') as the_time from dual;
ho eval 'for((i=0;i<10;i++));do sleep 1;echo "/";done >/tmp/myfifo.sql &'
@/tmp/myfifo

Save this as plus_tailf.sql and then run it, and the output looks like this…

plus_tailf

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 ….

--
-- Usage : tailf <scriptname> <iterations> <interval in seconds>
--
def fname=&1
def iter=&2
def interval=&3
def command = 'for((i=0;i<&iter;i++));do sleep &interval;echo "/";done >/tmp/myfifo.sql &'
!mkfifo /tmp/myfifo.sql
@&fname
!eval "&command"
@/tmp/myfifo
!rm /tmp/myfifo.sql

We’re going to save this in the SQLPATH directory as tailf.sql.
My SQLPATH is defined in my .bashrc as :

export SQLPATH=$HOME/sqlplus_scripts

We also need to save our query to get the current time into a file called time.sql. This can be in any directory.

select to_char(sysdate, 'HH24:MI:SS') as time_now
from dual
/

We can now invoke it from SQL*Plus ( or SQLcl) simply by running…

@tailf time.sql 10 1

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.

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