dave's blog

So my last post was about administration on Linux. After running Oracle on Windows for way too many years we migrated our Oracle systems to 64-bit Linux over the past year.

Now that we're on Linux I needed a lightweight way to capture and graph some performance data. The performance data was a mix of OS utilities and 3rd party utilities, but all the utilities output text, so I knew I could screen scrape the output, massage it a little, and save the data to a file. After I had the data in a file I knew I could run it through GnuPlot to graph it.

There may come a time when you need to return an error code from SQLPlus, either to a calling batch file, shell script, or Perl script.

SQLPlus has a WHENEVER directive available for handling errors it encounters. This command controls the behavior of SQLPlus when an OS or SQL error occurs. There are many options for this behavior.

More after the jump…

SQL errors include errors thrown by a single command entered into SQLPlus or an error raised by a PL/SQL block:

Did you know you can make your own Long Running Operation that is available to the V$SESSION_LONGOPS system view?

At the bottom of the Oracle documentation for DBMS_APPLICATION_INFO is some example code: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_appinf.htm#i999254

In a previous post I posted Perl source code for a script that monitors V$SESSION_LONGOPS and reports progress of all current long operations: TODO

– Dave

I've recently been working on improving the Database Change procedures and Deployment procedures where I work. We like to turn around releases every 2-4 weeks or so but sometimes the friction of keeping a sane and organized development process slows us down. At this point I would rather be slow than to introduce errors to our production environments but I am always looking for ways to improve accuracy and speed of our processes.

I've been looking for ways to automate running of Oracle SQLPlus PL/SQL scripts.Of course there are always batch and shell scripts but I'm looking for something less OS specific.Our Java build process is controlled by Apache Ant. It would be nice to include running of SQL scripts in this process. Here are some tools to help integrate SQLPLUS with ANT.

SQL Task
This task is best for executing bits of literal SQL. This task doesn't know anything about SQLPlus features such as formatting, variables, spooling, etc.

So I'm 60% of the way to my 9i OCP now. I passed 1ZO-031 today. I already had the 1ZO-007 test and a hands-on class under my belt.

So far I've been using the Certification Candidate Guide, Oracle Press books and of course the Oracle documentation. For my next two exams I got some Sybex books cheap off of Amazon.com that I'll be checking out.

Ever smell something that brings back a memory from way long ago?

I had the same sort of experience today but it was because I saw a software display screen.

I was in a popular discount store in the Southeast US today and the cashier was having some problems ringing up my order. I checked out the screen and saw a software program that I worked on 10 years ago. I used to work for a Point of Sale software company and we developed a custom POS software package for this client.

PerlCast.COM has posted a interview with Tim Bunce available here.

Audio is here: http://www.perlcast.com/audio/Perlcast_Interview_038.mp3

– Dave

One of the redeeming qualities of the Java Oracle Enterprise Manager client is the progress bar that comes up when you are viewing the long operations of a session. I wanted this functionality but didn’t want to wake up the big lug to get this info (it takes a long time to start up the OEM client and navigate to the screen to see the long operations progress bar).

So I decided to write a short script to emulate that functionality but take it one step further and show me info about _all_ the longops currently running on an instance.

I recently discovered that someone else is publishing some Perl utilities to monitor Oracle!

I've been using Perl to get things done with Oracle for a while and have found it extremely helpful. The current environment that I work in has a mixture of OSes so sometimes its easier to install Perl and DBI everywhere and write a simple Perl Script than to try to do something in DOS Batch for one client and then do it again in a bash shell script for another.

Sean Hull published a video of his talk about otop, the PowerPoint slides, and the downloadable source on his company's site: