Re: drop user hangs

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/