very active session, no SQL

From: Mladen Gogala <mgogala_at_allegientsystems.com>
Date: Tue, 04 Jan 2005 23:53:56 -0500
Message-ID: <41DB72E4.6070306@allegientsystems.com>

I am extremely confused, not for the first time in my life. I am sanitizing data in one
of my databases, for demo purposes. Unfortunately, the demo data in question consists
of BLOB files of up to 32MB in size, so I had to do a little trickery. The script below works like a charm, and itakes approximately 1 hour to complete. What confuses the heck out of me is the fact that the session is active, and I don't see any SQL in v$session:  SQL> ed
Wrote file afiedt.buf

  1 select u.username,u.osuser,u.program,u.status, s.sql_text   2 from v$session u,v$sql s

  3  where u.sid=27 and
  4        u.sql_address=s.address and
  5*       u.sql_hash_value=s.hash_value

SQL> / no rows selected

SQL>
Now, when I am not looking for SQL, the session is OK, and active:   1 select u.username,u.osuser,u.program,u.status   2 from v$session u
  3* where u.sid=27
SQL> /

USERNAME                       OSUSER
------------------------------ ------------------------------
PROGRAM                                          STATUS
------------------------------------------------ --------
FCC                            mgogala
perl_at_medo.noip.com (TNS V1-V3)                   ACTIVE

  1* select event from v$session_wait where sid=27 SQL> / EVENT

---

SQL*Net more data from client

So, I have an active session, which has one of those "idle" waits (joke will be apparent to anyone who takes the look at the script below) without executing
any SQL? How is it possible? Here is the script:

#!/usr/bin/perl -w
use strict;
use DBI;
use Getopt::Long;

my $user   = "fcc";
my $passwd = "***";
my $db     = "sales_demo";

my ( $rid,$file, $buffer, $ext );
my @rowids;

# Parse command line options
my $stat = GetOptions(

    "u|username=s" => \$user,
    "p|password=s" => \$passwd,
    "f|file=s"     => \$file,
    "e|ext=s"      => \$ext,
    "h|help|?"     => \&usage

);
if ( !defined($user) || !defined($passwd) || !defined($file) || !$stat ) {

    usage();
}

$ext =~ tr/[a-z]/[A-Z]/;

my $dbh = db_connect( $user, $passwd, $db );

# File is read as a whole
open( FL, "<", $file ) or die "Cannot open $file for read:$!\n"; $/ = undef;
$buffer = <FL>;
# Get the rowids of the rows with the right documents my $SEL = "select rowid from invoice_doc

           where upper(doc_filename) like \'%$ext\'";

my $UPD = "update invoice_doc

           set doc_file=:FL
           where rowid=:RID";

my $sth = $dbh->prepare($SEL);
$sth->execute();
while ( my @row = $sth->fetchrow_array() ) {

    ($rid) = @row;
    push @rowids, $rid;
}

my $upd = $dbh->prepare($UPD);
$upd->bind_param( ":FL", $buffer, DBI::SQL_BLOB ); $upd->bind_param( ":RID", $rid );
foreach $rid (@rowids) {

    $upd->execute();
    $dbh->commit();
}

END {
    $dbh->disconnect() if defined($dbh);
}

sub db_connect {

    my ( $username, $passwd, $db ) = ( @_, $ENV{"TWO_TASK"} );     my $dbh = DBI->connect( "dbi:Oracle:$db", $username, $passwd );

    $dbh->{AutoCommit}    = 0;
    $dbh->{RaiseError}    = 1;
    $dbh->{ora_check_sql} = 0;
    $dbh->{RowCacheSize}  = 16;

    return ($dbh);
}

sub usage {

    print q(update_file -> updates blob field in the database

      USAGE:update_file -u=user -p=passwd -f=file -e=ext
      OPTIONS: -u oracle username
               -p password for the above
               -f file to read the BLOB data from
               -e extension to replace          

);

    exit(0);
}

-- 
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 05 2005 - 00:43:14 CST