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
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@xxxxxxxxxxxxx (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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » very active session, no SQL
