ba6.us - Dave's Database Related Stuff

I started a new project for a utility I started writing. It has some basic functionality now but will probably increase in utility int he future.

It is a Perl script that extracts the SQL statements from an Oracle 10046 trace file. This is useful if you need to re-run SQL that was captured during a trace. It also helps reduce the amount of junk you have to slog through if you are interested in

trc2sql.pl - Extract SQL Statements From Oracle 10046 Trace File

-Dave

Purpose:

This Perl script will extract the SQL Statements out of an Oracle 10046 Trace File.

Usage:

trc2sql.pl < input.trc > output.sql

Source Code

trc2sql.pl :

#!/usr/bin/perl

# David Mann
# ba6.us
# Extract SQL from an Oracle SQL Trace File (10046)
#
# Usage:
# Data is read from STDIN
# trc2sql.pl < input.trc > output.sql
#
# Change log:
# 13-OCT-2011 : Release

$InSQL = 0;
while (<>) {
  $MyLine = $_;
  $LineNum = $LineNum + 1;

  # If END OF STMT found, stop printing
 Read more »

Purpose

Monitor Long Operations inside an Oracle Database using Perl/DBI. This can be useful for monitoring Table Scans, Index Rebuilds, and Exports.

Source Code

http://ba6.us/CodeExamples/Perl-LongOps

Looks like Oracle has finally come around and made it easier to incorporate the DBI module into the Oracle HTTP Server. The 10gR2 HTTP Server Administrators guide also has a laundry list of other modules that are enabled by default or easily turned on. Here is the mod_perl section of the 10gR2 HTTP Server Admininistrators Guide: click here

I just downloaded and installed the latest version of ActiveState Perl for Win32. After installation I started to hit the laundry list of post-install tasks and noticed that 1) there is a new graphical Perl Package Manager and 2) I couldn't install DBI and DBD-Oracle because they were already installed! No more looking for the old ESoftmatic drivers!

I've been using TextPad for writing Perl programs forever. TextPad is a pretty lightweight editor with a lot of decent features. Its nice because you can set up a PL document class with a syntax file to enable highlighting whenever you edit a *.pl file.

But today I'm working on a more complicated script with multiple functions. My main requirement is that there be an outline view so I can quickly jump between functions. Read more »

I know using Perl backticks call I can execute an OS command and easily get the output. I know using Perl system() call I can execute an OS command and easily get the return value. Today I found out a way to get both. This example is on Windows, but if you are on Unix, substitute 'ls' for 'dir'.

#!/bin/perl

# Execute dir command
$command = `dir`;

# Show the output of the command
#split by newlines, put into @alloutput array
@alloutput = split /\n/, $command;  

#loop once for each item in @alloutput array
foreach $outputline (@alloutput) {   
 Read more »

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. Read more »

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: Read more »