Re: Oracle Locking Up

Mark D Powell wrote:

> Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EAEC8D1.95FCED50_at_exxesolutions.com>...
> > Oracle 9.2.0.1.0 on a single CPU 1.7GHz Pentium IV
> >
> > One user doing SELECTS from various tables none with more than 10K rows
> > and here's what I get:
> >
> > SQL> select event, seconds_in_wait
> > 2 from v_$session_wait;
> >
> > EVENT SECONDS_IN_WAIT
> > ------------------------------------------------------ ---------------
> > null event 0
> > pmon timer 3
> > rdbms ipc message 0
> > rdbms ipc message 0
> > rdbms ipc message 154
> > rdbms ipc message 3
> > rdbms ipc message 3
> > smon timer 208
> > library cache pin 0
> > library cache lock 3
> > SQL*Net message from client 306
> > SQL*Net message from client 926
> > SQL*Net message from client 256
> >
> > The lock up and these waits are outrageous. Any ideas will be
> > appreciated.
> >
> > It is a student's machine and I would like to try to fix the problem by
> > email or phone rather than driving to their house.
> >
> > Thanks.
>
> Daniel, is the problem repeatable after booting the machine?
> Do queries against the rdbms dictionary run OK?
>
> HTH -- Mark D Powell --

The problem is sporadic. When it locks up Oracle can be shut down with another SQL*Plus session / as sysdba and shutdown immediate. Then it may happen again hours later. Same goes for rebooting the machine. It will happen again but hours later.

And what is strange is that it never seems to interfere with a SQL statement that is executing. The statement will finish, commit but never return to the SQL> prompt.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)