Re: drop user hangs

Joel Garry wrote:

> Rainer Herbst <rherbst_@_rz.uni-potsdam.de> wrote in message news:<bif59m$i3m$1_at_zeppelin.rz.uni-potsdam.de>...
> > > Hangs for ever? You're right that you can't drop a user if they are logged
> > > in, so how are you certain that they are not logged in? When you say it
> > > hangs, and produces no error message, have you nevertheless checked the
> > > alert log for any possible warning messages.
> > >
> > No errors or warnings in the alert log. V$SESSION_WAIT shows the 'null
> > event' for this session.
> >
> > > It occurs to me that since this is 9i, if you were very short of space in
> > > things like the SYSTEM rollback segment, and if somehow your session had
> > > been placed into RESUMABLE mode, then the drop of the user (which involves
> > > doing deletes from various data dictionary tables, and thus generating some
> > > undo/rollback) could run into out-of-space errors, which the 'resumable'
> > > setting would cause to hang instead. If so, the alert log will tell you.
> > >
> > Session set explicitly to DISABLE RESUMABLE, but the same behaviour.
> >
> > There might be a problem because of the SYSTEM tablespace is filled to
> > 98%, but
> > - the datafile is AUTOEXTEND ON and
> > - the database runs in UNDO_MANAGEMENT = AUTO.
> > In my understanding, I do not have to bother about the rollback segments
> > as far as there is enough space on the disk and the datafiles are not on
> > their max extends limit? Again, if any problems occure, there should be
> > a message in the alert log?
>
> Well, ORA-155x is supposed to be long-gone with undo, but I'm
> wondering if something is so messed up it hangs and can't tell you
> about it. That could be an error that is supposed to come back to
> you, rather than the alert log.
>
> Are you sure you aren't running into a maxsize parameter on your
> autoextend ts? I would be worried about a 98% filled system ts just
> on general principle, but then again I'm old-fashioned.
>
> jg
> --
> @home.com is bogus.
> http://www.zone-h.org/en/news/read/id=3287/

UNDO decreases the incidence of ORA-01555 but does not eliminate it. And most certainly can not solve the problem of badly written code such as incremental commits inside a loop.

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