Using Perl to Monitor v$session_longops

Description:

This script continuously watches and interprets the contents of the v$Session_LongOps view.

One of the redeeming qualities of the Java Oracle Enterprise Manager 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 and navigate to the screen to see the single 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.

This can be useful when you are performing long operations on your database instance like:
o Exporting
o Rebuilding indexes
o Long running updates
o Long running queries

From Oracle MetaLink Note 180924.1:

This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.
To monitor query execution progress, you must be using the cost-based optimizer and you must:
o Set the TIMED_STATISTICS or SQL_TRACE parameter to TRUE
o Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package

Screen shot:

Running the Script:

perl WatchLongops.pl username/password@tnsname

Hit Ctrl-c to stop the script.

Source Code:
longops.pl:

To Do:

o Secure the script, username and password on the command line is not so great on Unix (visible with ps -ef).
o System "cls" only works in DOS-land. Investigate eliminating this system call and using Curses for prettier output.
o Add ability to configure refresh time.
o Add a friendlier way to exit besides Ctrl-c.(May require ReadTerm CPAN package which is not included with Oracle's Perl by default)