Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Tailing the Alert Log with SQLcl

2 Mins Read

Hopefully you’ve heard about the new SQL*Plus we’re building.

And hopefully you’ve heard about the new REPEAT command we put into it yesterday.

Here’s a quick example of how to build a little ‘monitor’ in just 2 commands, a total of 5 lines of code.

The SQL

SELECT To_Char(Originating_Timestamp, 'DD-MON-YYYY HH24:MI:SSxFF') Time_Entry,
       substr(message_text, 0, 75) || '...' ABBR_MESSAGE_TEXT
FROM X$dbgalertext
ORDER BY Originating_Timestamp DESC
fetch FIRST 10 ROWS ONLY;

So run that, make sure you like the formatting, and the data of course 🙂

Then to turn that into a refreshing report, run this:

SQL>repeat 100 0.5

And that’s it.

It will run that SQL (as it was the last thing in my SQLcl buffer), 100 times, every 0.5 seconds.

The screen refreshes with an update of what iteration you’re on.

I’ve also set the sqlformat to ANSICONSOLE for the nicer auto-spacing and column headers.

Your SQL is probably better than mine, but this is still pretty cool.
Your SQL is probably better than mine, but this is still pretty cool.

What, you want an animated demo?

Ok, ok.

Here I’m causing some Alert Log entries to be added by forcing a log switch on the Container database.

A half-second refresh, for 100 iterations, so this would run for 50 seconds. I think.
A half-second refresh, for 100 iterations, so this would run for 50 seconds. I think.

About that FETCH FIRST 10 ROWS bit

That syntax is new for 12c. And it’s awesome. It’s much nicer than doing a WHERE ROWNUM < 11 or whatever. You can skip rows, read rows, etc. Sorry I didn't make this more clear when I first posted this. Or maybe I wanted to you to try it and see this cool technology for yourself 🙂 Or maybe I was lazy. In my defense, we’ve been talking about this since 2013.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

3 Comments

  1. Zohar Elkayam Reply

    Hey Jeff,

    The command is really cool but it needs a little tweeking: since some commands can return with the same originating timestamp, their lines get mixed up when returned from the query.

    You can add order by indx desc to show the correct order of the lines:

    SELECT To_Char(Originating_Timestamp, ‘DD-MON-YYYY HH24:MI:SSxFF’) Time_Entry, indx,
    substr(trim(message_text), 0, 75) || ‘…’ ABBR_MESSAGE_TEXT
    FROM X$dbgalertext
    ORDER BY Originating_Timestamp DESC, indx desc
    fetch FIRST 20 ROWS ONLY;

    Cheers,
    Zohar

  2. Arun Reply

    Hi Jeff,

    Are we sure about that “fetch FIRST 10 ROWS ONLY” ? As far as I know, the command will not work till 11g.

Write A Comment