very active session, no SQL

  • From: Mladen Gogala <mgogala@xxxxxxxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 04 Jan 2005 23:53:56 -0500

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

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@xxxxxxxxxxxxx (TNS V1-V3)                   ACTIVE

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

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 
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 ) {

$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);
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) {

    $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          

Mladen Gogala
Oracle DBA


Other related posts:

  • » very active session, no SQL