Mladen, I have been and I am still very confused about V$SESSION.STATUS. What I am seeing in one of my Oracle Portal databases is: - STATUS in V$SESSION is ACTIVE - The dedicated server process does not use any CPU. - I cannot find the SQL in V$SQL - V$SESSION_WAIT says: WAITED SHORT TIME - V$SESSION_WAIT.SECONDS_IN_WAIT keeps increasing (My interpretation is that it means, that the wait state did not change during the last SECONDS_IN_WAIT seconds - typically hours). - ALTER SYSTEM KILL SESSION gives ORA-31 Session marked for kill What I think is the situation is, that the process is hanging (i.e. waiting for something that will never happen). Unfortunately the thing we are waiting for has not been instrumented (yet). Sessionstate dumps did not make me cleverer. My two cents is: Try to look at CPU consumption both in OS and V$SESSTAT. Have a very close look on the wait interface. Are you waiting for something? (V$SESSION_WAIT.STATE =3D WAITING). Have you been waiting for something, but are not waiting right now (WAITED SHORT TIME, WAITED KNOW TIME, or WAITED UNKNOWN TIME). If time disappear you might be waiting for something,=20 that is not instrumented in the wait interface. Hope this helps you to get a little less confused. Regards Jesper Haure Norrevang -----Oprindelig meddelelse----- Fra: oracle-l-bounce@xxxxxxxxxxxxx = [mailto:oracle-l-bounce@xxxxxxxxxxxxx] P=E5 vegne af Mladen Gogala Sendt: 5. januar 2005 05:54 Til: oracle-l@xxxxxxxxxxxxx Emne: very active session, no SQL I am extremely confused, not for the first time in my life. I am=20 sanitizing data in one of my databases, for demo purposes. Unfortunately, the demo data in=20 question consists of BLOB files of up to 32MB in size, so I had to do a little trickery.=20 The script below works like a charm, and itakes approximately 1 hour to=20 complete. What confuses the heck out of me is the fact that the session=20 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=3D27 and 4 u.sql_address=3Ds.address and 5* u.sql_hash_value=3Ds.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=3D27 SQL> / USERNAME OSUSER ------------------------------ ------------------------------ PROGRAM STATUS ------------------------------------------------ -------- FCC mgogala perl@xxxxxxxxxxxxx (TNS V1-V3) ACTIVE 1* select event from v$session_wait where sid=3D27 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=20 executing any SQL? How is it possible? Here is the script: #!/usr/bin/perl -w use strict; use DBI; use Getopt::Long; my $user =3D "fcc"; my $passwd =3D "***"; my $db =3D "sales_demo"; my ( $rid,$file, $buffer, $ext ); my @rowids; # Parse command line options my $stat =3D GetOptions( "u|username=3Ds" =3D> \$user, "p|password=3Ds" =3D> \$passwd, "f|file=3Ds" =3D> \$file, "e|ext=3Ds" =3D> \$ext, "h|help|?" =3D> \&usage ); if ( !defined($user) || !defined($passwd) || !defined($file) || !$stat ) = { usage(); } $ext =3D~ tr/[a-z]/[A-Z]/; my $dbh =3D db_connect( $user, $passwd, $db ); # File is read as a whole open( FL, "<", $file ) or die "Cannot open $file for read:$!\n"; $/ =3D undef; $buffer =3D <FL>; # Get the rowids of the rows with the right documents my $SEL =3D "select rowid from invoice_doc where upper(doc_filename) like \'%$ext\'"; my $UPD =3D "update invoice_doc set doc_file=3D:FL where rowid=3D:RID"; my $sth =3D $dbh->prepare($SEL); $sth->execute(); while ( my @row =3D $sth->fetchrow_array() ) { ($rid) =3D @row; push @rowids, $rid; } my $upd =3D $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 ) =3D ( @_, $ENV{"TWO_TASK"} ); my $dbh =3D DBI->connect( "dbi:Oracle:$db", $username, $passwd ); $dbh->{AutoCommit} =3D 0; $dbh->{RaiseError} =3D 1; $dbh->{ora_check_sql} =3D 0; $dbh->{RowCacheSize} =3D 16; return ($dbh); } sub usage { print q(update_file -> updates blob field in the database USAGE:update_file -u=3Duser -p=3Dpasswd -f=3Dfile -e=3Dext OPTIONS: -u oracle username -p password for the above -f file to read the BLOB data from -e extension to replace =20 ); exit(0); } --=20 Mladen Gogala Oracle DBA -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l