Re: Exists in v$session, not in v$process

Connor,

Since no-one seems to have answered this yet, I'll throw in my first thoughts.

The interesting thing about your output is that the serial number is increasing, presumably fairly rapidly.

Since the identity of the session is actually the combination of the sid and serial#, this suggests that something keeps connecting and disconnecting for some reason.

The next time is happens I would have a look at pmon to see if it is chewing up CPU, and there is an event you can fire at it to kick off a trace file (can't remember the number offhand, but you know how to find it out).

The other thing that might be interesting is to drop down from v$process to x$ksupr, to see if the paddr in v$session matches to a 'hidden' process.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Connor McDonald wrote in message <37A6D75C.2D02_at_yahoo.com>...
>Can anyone put explanation on this scenario...
>
>- User initiates large job from PC.
>- Doesn't like the fact that its taking a long time and Ctrl-Alt-Del's
>
>Main table they were updating remains locked...
>
>So some checking reveals the following
>
>SQL> select * from v$version;
>
>BANNER
>----------------------------------------------------------------
>Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
>PL/SQL Release 8.0.5.0.0 - Production
>CORE Version 4.0.5.0.0 - Production
>TNS for SEQUENT DYNIX/ptx: Version 8.0.5.0.0 - Production
>NLSRTL Version 3.3.2.0.0 - Production
>
>SQL> select sid, serial#, status, command
> 2 from v$session
> 3 where sid = 11
> 4 /
>
>SQL> /
>
> SID SERIAL# STATUS COMMAND
>---------- ---------- -------- ----------
> 11 9629 SNIPED 0
>
>SQL> /
>
> SID SERIAL# STATUS COMMAND
>---------- ---------- -------- ----------
> 11 9631 SNIPED 0
>
>SQL> /
>
> SID SERIAL# STATUS COMMAND
>---------- ---------- -------- ----------
> 11 9632 SNIPED 0
>
>SQL> select p.SPID
> 2 from v$session s, v$process p
> 3 where s.PADDR = p.ADDR
> 4* and s.SID =11
>SQL> /
>
>no rows selected
>
>
>
>So "SNIPED" tells me that the connection is waiting on the client (?).
>There is no O.S process either by 'ps' or in v$process yet the SID
>still remains in v$session. COMMAND=0 tells me nothings going on yet
>testing the SQL_ADDRESS shows that it is "still" performing the large
>update...and of course the table is still locked.
>
>Any ideas ? Is the SID still there whilst rollback occurs due to the
>dead process ?
>
>Cheers
>Connor
>
>
>
>--
>===========================================
>Connor McDonald
>"These views mine, no-one elses etc etc"
>connor_mcdonald_at_yahoo.com
>
>"Some days you're the pigeon, and some days you're the statue."
Received on Fri Aug 06 1999 - 10:20:40 CDT